<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
	<title>SQLAlchemy 0.4 Documentation - SQL Expression Language Tutorial</title>
	
    <link rel="stylesheet" href="style.css"></link>
    <link rel="stylesheet" href="docs.css"></link>
    <link href="syntaxhighlight.css" rel="stylesheet" type="text/css"></link>
    <script src="scripts.js"></script>



</head>
<body>








<div id="topanchor"><a name="top">&nbsp;</a></div>


<h1>SQLAlchemy 0.4 Documentation</h1>

<div id="pagecontrol"><a href="index.html">Multiple Pages</a> | <a href="documentation.html">One Page</a></div>

<div class="versionheader">Version: 0.4.8   Last Updated: 10/12/08 13:33:19</div>







<A name=""></a>


    <div class="topnav">

    
    <div class="navbanner">
        <a href="index.html" class="totoc">Table of Contents</a>
        
    <div class="prevnext">

            
            Previous: <a href="ormtutorial.html">Object Relational Tutorial</a>

               |   
            Next: <a href="mappers.html">Mapper Configuration</a>
    </div>

        <h2>SQL Expression Language Tutorial</h2>
    </div>

	
	
    <ul>
        
        <li><a style="" href="sqlexpression.html#sql_version">Version Check</a></li>

        
        <li><a style="" href="sqlexpression.html#sql_connecting">Connecting</a></li>

        
        <li><a style="" href="sqlexpression.html#sql_tables">Define and Create Tables</a></li>

        
        <li><a style="" href="sqlexpression.html#sql_insert">Insert Expressions</a></li>

        
        <li><a style="" href="sqlexpression.html#sql_executing">Executing</a></li>

        
        <li><a style="" href="sqlexpression.html#sql_execmany">Executing Multiple Statements</a></li>

        
        <li><a style="" href="sqlexpression.html#sql_connectionless">Connectionless / Implicit Execution</a></li>

        
        <li><a style="" href="sqlexpression.html#sql_selecting">Selecting</a></li>

        
        <li><a style="" href="sqlexpression.html#sql_operators">Operators</a></li>

        
        <li><a style="" href="sqlexpression.html#sql_conjunctions">Conjunctions</a></li>

        
        <li><a style="" href="sqlexpression.html#sql_text">Using Text</a></li>

        
        <li><a style="" href="sqlexpression.html#sql_aliases">Using Aliases</a></li>

        
        <li><a style="" href="sqlexpression.html#sql_joins">Using Joins</a></li>

        
        <li><a style="" href="sqlexpression.html#sql_transform">Intro to Generative Selects and Transformations</a></li>

        
        <li><a style="" href="sqlexpression.html#sql_everythingelse">Everything Else</a></li>

	        <li>
                
    <ul>
        
        <li><a style="" href="sqlexpression.html#sql_everythingelse_bindparams">Bind Parameter Objects</a></li>

        
        <li><a style="" href="sqlexpression.html#sql_everythingelse_functions">Functions</a></li>

        
        <li><a style="" href="sqlexpression.html#sql_everythingelse_unions">Unions and Other Set Operations</a></li>

        
        <li><a style="" href="sqlexpression.html#sql_everythingelse_scalar">Scalar Selects</a></li>

        
        <li><a style="" href="sqlexpression.html#sql_everythingelse_correlated">Correlated Subqueries</a></li>

        
        <li><a style="" href="sqlexpression.html#sql_everythingelse_ordering">Ordering, Grouping, Limiting, Offset...ing...</a></li>

    </ul>

	        </li>
        
        <li><a style="" href="sqlexpression.html#sql_update">Updates</a></li>

	        <li>
                
    <ul>
        
        <li><a style="" href="sqlexpression.html#sql_update_correlated">Correlated Updates</a></li>

    </ul>

	        </li>
        
        <li><a style="" href="sqlexpression.html#sql_delete">Deletes</a></li>

        
        <li><a style="" href="sqlexpression.html#sql_reference">Further Reference</a></li>

    </ul>

	</div>











    
    <A name="sql"></a>
    
    <div class="sectionL1">

    
    

<p>This tutorial will cover SQLAlchemy SQL Expressions, which are Python constructs that represent SQL statements.  The tutorial is in doctest format, meaning each <code>&gt;&gt;&gt;</code> line represents something you can type at a Python command prompt, and the following text represents the expected return value.  The tutorial has no prerequisites.
</p>


    
    <A name="sql_version"></a>
    
    <div class="sectionL2">

    <h3>Version Check</h3>
    
    

<p>A quick check to verify that we are on at least <strong>version 0.4</strong> of SQLAlchemy:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">import </span><span class="python_name">sqlalchemy</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">sqlalchemy</span><span class="python_operator">.</span><span class="python_name">__version__ </span><span class="python_operator">
</span><span class="python_number">0.4.0</span><span class="python_operator">
</span></pre>
    </div>



            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="sql_connecting"></a>
    
    <div class="sectionL2">

    <h3>Connecting</h3>
    
    

<p>For this tutorial we will use an in-memory-only SQLite database.   This is an easy way to test things without needing to have an actual database defined anywhere.  To connect we use <code>create_engine()</code>:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">from </span><span class="python_name">sqlalchemy </span><span class="python_keyword">import </span><span class="python_name">create_engine</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">engine </span><span class="python_operator">= </span><span class="python_name">create_engine</span><span class="python_enclosure">(</span><span class="python_literal">'sqlite:///:memory:'</span><span class="python_operator">, </span><span class="python_name">echo</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>The <code>echo</code> flag is a shortcut to setting up SQLAlchemy logging, which is accomplished via Python's standard <code>logging</code> module.  With it enabled, we'll see all the generated SQL produced.  If you are working through this tutorial and want less output generated, set it to <code>False</code>.   This tutorial will format the SQL behind a popup window so it doesn't get in our way; just click the "SQL" links to see whats being generated.
</p>



            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="sql_tables"></a>
    
    <div class="sectionL2">

    <h3>Define and Create Tables</h3>
    
    

<p>The SQL Expression Language constructs its expressions in most cases against table columns.  In SQLAlchemy, a column is most often represented by an object called <code>Column</code>, and in all cases a <code>Column</code> is associated with a <code>Table</code>.  A collection of <code>Table</code> objects and their associated child objects is referred to as <strong>database metadata</strong>.  In this tutorial we will explicitly lay out several <code>Table</code> objects, but note that SA can also "import" whole sets of <code>Table</code> objects automatically from an existing database (this process is called <strong>table reflection</strong>).
</p>
<p>We define our tables all within a catalog called <code>MetaData</code>, using the <code>Table</code> construct, which resembles regular SQL CREATE TABLE statements.  We'll make two tables, one of which represents "users" in an application, and another which represents zero or more "email addreses" for each row in the "users" table:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">from </span><span class="python_name">sqlalchemy </span><span class="python_keyword">import </span><span class="python_name">Table</span><span class="python_operator">, </span><span class="python_name">Column</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_operator">, </span><span class="python_name">MetaData</span><span class="python_operator">, </span><span class="python_name">ForeignKey</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">metadata </span><span class="python_operator">= </span><span class="python_name">MetaData</span><span class="python_enclosure">()</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">users </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'users'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">,</span>
<span class="python_operator">...     </span><span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
<span class="python_operator">...     </span><span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'name'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">40</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
<span class="python_operator">...     </span><span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'fullname'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">100</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
<span class="python_operator">... </span><span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">addresses </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'addresses'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">, </span>
<span class="python_operator">...   </span><span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
<span class="python_operator">...   </span><span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'user_id'</span><span class="python_operator">, </span><span class="python_name">None</span><span class="python_operator">, </span><span class="python_name">ForeignKey</span><span class="python_enclosure">(</span><span class="python_literal">'users.id'</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
<span class="python_operator">...   </span><span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'email_address'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">50</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_name">nullable</span><span class="python_operator">=</span><span class="python_name">False</span><span class="python_enclosure">)</span>
<span class="python_operator">...  </span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>All about how to define <code>Table</code> objects, as well as how to create them from an existing database automatically, is described in <a href="metadata.html">Database Meta Data</a>.
</p>
<p>Next, to tell the <code>MetaData</code> we'd actually like to create our selection of tables for real inside the SQLite database, we use <code>create_all()</code>, passing it the <code>engine</code> instance which points to our database.  This will check for the presence of each table first before creating, so it's safe to call multiple times:
</p>

    

    <div class="code">
        <pre>
<span class="python_literal"><a href="javascript:togglePopbox('popbox_1', 'show', 'hide')" class="codepoplink">sql</a>&gt;&gt;&gt; </span><span class="python_name">metadata</span><span class="python_operator">.</span><span class="python_name">create_all</span><span class="python_enclosure">(</span><span class="python_name">engine</span><span class="python_enclosure">)</span><span class="python_operator"> </span></pre><div id="popbox_1_div" class="codepop" style="display:none;">PRAGMA table_info(&quot;users&quot;)<br/>
{}<br/>
PRAGMA table_info(&quot;addresses&quot;)<br/>
{}<br/>
CREATE TABLE users (<br/>
    id INTEGER NOT NULL, <br/>
    name VARCHAR(40), <br/>
    fullname VARCHAR(100), <br/>
    PRIMARY KEY (id)<br/>
)<br/>
{}<br/>
COMMIT<br/>
CREATE TABLE addresses (<br/>
    id INTEGER NOT NULL, <br/>
    user_id INTEGER, <br/>
    email_address VARCHAR(50) NOT NULL, <br/>
    PRIMARY KEY (id), <br/>
     FOREIGN KEY(user_id) REFERENCES users (id)<br/>
)<br/>
{}<br/>
COMMIT</div><pre><span class="python_operator"></span></pre>
    </div>



            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="sql_insert"></a>
    
    <div class="sectionL2">

    <h3>Insert Expressions</h3>
    
    

<p>The first SQL expression we'll create is the <code>Insert</code> construct, which represents an INSERT statement.   This is typically created relative to its target table:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">ins </span><span class="python_operator">= </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">insert</span><span class="python_enclosure">()</span><span class="python_operator">
</span></pre>
    </div>
<p>To see a sample of the SQL this construct produces, use the <code>str()</code> function:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">str</span><span class="python_enclosure">(</span><span class="python_name">ins</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_literal">'INSERT INTO users (id, name, fullname) VALUES (:id, :name, :fullname)'</span><span class="python_operator">
</span></pre>
    </div>
<p>Notice above that the INSERT statement names every column in the <code>users</code> table.  This can be limited by using the <code>values</code> keyword, which establishes the VALUES clause of the INSERT explicitly:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">ins </span><span class="python_operator">= </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">insert</span><span class="python_enclosure">(</span><span class="python_name">values</span><span class="python_operator">=</span><span class="python_enclosure">{</span><span class="python_literal">'name'</span><span class="python_operator">:</span><span class="python_literal">'jack'</span><span class="python_operator">, </span><span class="python_literal">'fullname'</span><span class="python_operator">:</span><span class="python_literal">'Jack Jones'</span><span class="python_enclosure">})</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">str</span><span class="python_enclosure">(</span><span class="python_name">ins</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_literal">'INSERT INTO users (name, fullname) VALUES (:name, :fullname)'</span><span class="python_operator">
</span></pre>
    </div>
<p>Above, while the <code>values</code> keyword limited the VALUES clause to just two columns, the actual data we placed in <code>values</code> didn't get rendered into the string; instead we got named bind parameters.  As it turns out, our data <em>is</em> stored within our <code>Insert</code> construct, but it typically only comes out when the statement is actually executed; since the data consists of literal values, SQLAlchemy automatically generates bind parameters for them.  We can peek at this data for now by looking at the compiled form of the statement:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">ins</span><span class="python_operator">.</span><span class="python_name">compile</span><span class="python_enclosure">()</span><span class="python_operator">.</span><span class="python_name">params </span><span class="python_operator">
</span><span class="python_enclosure">{</span><span class="python_literal">'fullname'</span><span class="python_operator">: </span><span class="python_literal">'Jack Jones'</span><span class="python_operator">, </span><span class="python_literal">'name'</span><span class="python_operator">: </span><span class="python_literal">'jack'</span><span class="python_enclosure">}</span><span class="python_operator">
</span></pre>
    </div>



            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="sql_executing"></a>
    
    <div class="sectionL2">

    <h3>Executing</h3>
    
    

<p>The interesting part of an <code>Insert</code> is executing it.  In this tutorial, we will generally focus on the most explicit method of executing a SQL construct, and later touch upon some "shortcut" ways to do it.  The <code>engine</code> object we created is a repository for database connections capable of issuing SQL to the database.  To acquire a connection, we use the <code>connect()</code> method:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">conn </span><span class="python_operator">= </span><span class="python_name">engine</span><span class="python_operator">.</span><span class="python_name">connect</span><span class="python_enclosure">()</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">conn </span><span class="python_operator">
&lt;</span><span class="python_name">sqlalchemy</span><span class="python_operator">.</span><span class="python_name">engine</span><span class="python_operator">.</span><span class="python_name">base</span><span class="python_operator">.</span><span class="python_name">Connection object at </span><span class="python_number">0x</span><span class="python_operator">...&gt;
</span></pre>
    </div>
<p>The <code>Connection</code> object represents an actively checked out DBAPI connection resource.  Lets feed it our <code>Insert</code> object and see what happens:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">result </span><span class="python_operator">= </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">ins</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre><div class="codepop">INSERT INTO users (name, fullname) VALUES (?, ?)<br/>
['jack', 'Jack Jones']<br/>
COMMIT</div><pre>

<span class="python_operator"></span></pre>
    </div>
<p>So the INSERT statement was now issued to the database.  Although we got positional "qmark" bind parameters instead of "named" bind parameters in the output.  How come ?  Because when executed, the <code>Connection</code> used the SQLite <strong>dialect</strong> to help generate the statement; when we use the <code>str()</code> function, the statement isn't aware of this dialect, and falls back onto a default which uses named parameters. We can view this manually as follows:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">ins</span><span class="python_operator">.</span><span class="python_name">bind </span><span class="python_operator">= </span><span class="python_name">engine</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">str</span><span class="python_enclosure">(</span><span class="python_name">ins</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_literal">'INSERT INTO users (name, fullname) VALUES (?, ?)'</span><span class="python_operator">
</span></pre>
    </div>
<p>What about the <code>result</code> variable we got when we called <code>execute()</code> ?  As the SQLAlchemy <code>Connection</code> object references a DBAPI connection, the result, known as a <code>ResultProxy</code> object, is analogous to the DBAPI cursor object.  In the case of an INSERT, we can get important information from it, such as the primary key values which were generated from our statement:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">result</span><span class="python_operator">.</span><span class="python_name">last_inserted_ids</span><span class="python_enclosure">()</span><span class="python_operator">
</span><span class="python_enclosure">[</span><span class="python_number">1</span><span class="python_enclosure">]</span><span class="python_operator">
</span></pre>
    </div>
<p>The value of <code>1</code> was automatically generated by SQLite, but only because we did not specify the <code>id</code> column in our <code>Insert</code> statement; otherwise, our explicit value would have been used.   In either case, SQLAlchemy always knows how to get at a newly generated primary key value, even though the method of generating them is different across different databases; each databases' <code>Dialect</code> knows the specific steps needed to determine the correct value (or values; note that <code>last_inserted_ids()</code> returns a list so that it supports composite primary keys).
</p>



            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="sql_execmany"></a>
    
    <div class="sectionL2">

    <h3>Executing Multiple Statements</h3>
    
    

<p>Our insert example above was intentionally a little drawn out to show some various behaviors of expression language constructs.  In the usual case, an <code>Insert</code> statement is usually compiled against the parameters sent to the <code>execute()</code> method on <code>Connection</code>, so that there's no need to use the <code>values</code> keyword with <code>Insert</code>.  Lets create a generic <code>Insert</code> statement again and use it in the "normal" way:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">ins </span><span class="python_operator">= </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">insert</span><span class="python_enclosure">()</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">ins</span><span class="python_operator">, </span><span class="python_name">id</span><span class="python_operator">=</span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_name">name</span><span class="python_operator">=</span><span class="python_literal">'wendy'</span><span class="python_operator">, </span><span class="python_name">fullname</span><span class="python_operator">=</span><span class="python_literal">'Wendy Williams'</span><span class="python_enclosure">) </span><span class="python_operator">
</span></pre><div class="codepop">INSERT INTO users (id, name, fullname) VALUES (?, ?, ?)<br/>
[2, 'wendy', 'Wendy Williams']<br/>
COMMIT</div><pre>

<span class="python_operator">&lt;</span><span class="python_name">sqlalchemy</span><span class="python_operator">.</span><span class="python_name">engine</span><span class="python_operator">.</span><span class="python_name">base</span><span class="python_operator">.</span><span class="python_name">ResultProxy object at </span><span class="python_number">0x</span><span class="python_operator">...&gt;
</span></pre>
    </div>
<p>Above, because we specified all three columns in the the <code>execute()</code> method, the compiled <code>Insert</code> included all three columns.  The <code>Insert</code> statement is compiled at execution time based on the parameters we specified; if we specified fewer parameters, the <code>Insert</code> would have fewer entries in its VALUES clause.
</p>
<p>To issue many inserts using DBAPI's <code>executemany()</code> method, we can send in a list of dictionaries each containing a distinct set of parameters to be inserted, as we do here to add some email addresses:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">insert</span><span class="python_enclosure">()</span><span class="python_operator">, </span><span class="python_enclosure">[ </span>
<span class="python_operator">...    </span><span class="python_enclosure">{</span><span class="python_literal">'user_id'</span><span class="python_operator">: </span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">'email_address' </span><span class="python_operator">: </span><span class="python_literal">'jack@yahoo.com'</span><span class="python_enclosure">}</span><span class="python_operator">,</span>
<span class="python_operator">...    </span><span class="python_enclosure">{</span><span class="python_literal">'user_id'</span><span class="python_operator">: </span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">'email_address' </span><span class="python_operator">: </span><span class="python_literal">'jack@msn.com'</span><span class="python_enclosure">}</span><span class="python_operator">,</span>
<span class="python_operator">...    </span><span class="python_enclosure">{</span><span class="python_literal">'user_id'</span><span class="python_operator">: </span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_literal">'email_address' </span><span class="python_operator">: </span><span class="python_literal">'www@www.org'</span><span class="python_enclosure">}</span><span class="python_operator">,</span>
<span class="python_operator">...    </span><span class="python_enclosure">{</span><span class="python_literal">'user_id'</span><span class="python_operator">: </span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_literal">'email_address' </span><span class="python_operator">: </span><span class="python_literal">'wendy@aol.com'</span><span class="python_enclosure">}</span><span class="python_operator">,</span>
<span class="python_operator">... </span><span class="python_enclosure">])</span><span class="python_operator">
</span></pre><div class="codepop">INSERT INTO addresses (user_id, email_address) VALUES (?, ?)<br/>
[[1, 'jack@yahoo.com'], [1, 'jack@msn.com'], [2, 'www@www.org'], [2, 'wendy@aol.com']]<br/>
COMMIT</div><pre>

<span class="python_operator">&lt;</span><span class="python_name">sqlalchemy</span><span class="python_operator">.</span><span class="python_name">engine</span><span class="python_operator">.</span><span class="python_name">base</span><span class="python_operator">.</span><span class="python_name">ResultProxy object at </span><span class="python_number">0x</span><span class="python_operator">...&gt;
</span></pre>
    </div>
<p>Above, we again relied upon SQLite's automatic generation of primary key identifiers for each <code>addresses</code> row.
</p>
<p>When executing multiple sets of parameters, each dictionary must have the <strong>same</strong> set of keys; i.e. you cant have fewer keys in some dictionaries than others.  This is because the <code>Insert</code> statement is compiled against the <strong>first</strong> dictionary in the list, and it's assumed that all subsequent argument dictionaries are compatible with that statement.
</p>



            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="sql_connectionless"></a>
    
    <div class="sectionL2">

    <h3>Connectionless / Implicit Execution</h3>
    
    

<p>We're executing our <code>Insert</code> using a <code>Connection</code>.  There's two options that allow you to not have to deal with the connection part.  You can execute in the <strong>connectionless</strong> style, using the engine, which opens and closes a connection for you:
</p>

    

    <div class="code">
        <pre>
<span class="python_literal"><a href="javascript:togglePopbox('popbox_2', 'show', 'hide')" class="codepoplink">sql</a>&gt;&gt;&gt; </span><span class="python_name">result </span><span class="python_operator">= </span><span class="python_name">engine</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">insert</span><span class="python_enclosure">()</span><span class="python_operator">, </span><span class="python_name">name</span><span class="python_operator">=</span><span class="python_literal">'fred'</span><span class="python_operator">, </span><span class="python_name">fullname</span><span class="python_operator">=</span><span class="python_literal">"Fred Flintstone"</span><span class="python_enclosure">)</span><span class="python_operator"></span></pre><div id="popbox_2_div" class="codepop" style="display:none;">INSERT INTO users (name, fullname) VALUES (?, ?)<br/>
['fred', 'Fred Flintstone']<br/>
COMMIT</div><pre><span class="python_operator"></span></pre>
    </div>
<p>and you can save even more steps than that, if you connect the <code>Engine</code> to the <code>MetaData</code> object we created earlier.  When this is done, all SQL expressions which involve tables within the <code>MetaData</code> object will be automatically <strong>bound</strong> to the <code>Engine</code>.  In this case, we call it  <strong>implicit execution</strong>:
</p>

    

    <div class="code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">metadata</span><span class="python_operator">.</span><span class="python_name">bind </span><span class="python_operator">= </span><span class="python_name">engine</span><span class="python_operator">
</span><span class="python_literal"><a href="javascript:togglePopbox('popbox_3', 'show', 'hide')" class="codepoplink">sql</a>&gt;&gt;&gt; </span><span class="python_name">result </span><span class="python_operator">= </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">insert</span><span class="python_enclosure">()</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">name</span><span class="python_operator">=</span><span class="python_literal">"mary"</span><span class="python_operator">, </span><span class="python_name">fullname</span><span class="python_operator">=</span><span class="python_literal">"Mary Contrary"</span><span class="python_enclosure">)</span><span class="python_operator"></span></pre><div id="popbox_3_div" class="codepop" style="display:none;">INSERT INTO users (name, fullname) VALUES (?, ?)<br/>
['mary', 'Mary Contrary']<br/>
COMMIT</div><pre><span class="python_operator"></span></pre>
    </div>
<p>When the <code>MetaData</code> is bound, statements will also compile against the engine's dialect.  Since a lot of the examples here assume the default dialect, we'll detach the engine from the metadata which we just attached:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">metadata</span><span class="python_operator">.</span><span class="python_name">bind </span><span class="python_operator">= </span><span class="python_name">None</span><span class="python_operator">
</span></pre>
    </div>
<p>Detailed examples of connectionless and implicit execution are available in the "Engines" chapter: <a href="dbengine.html#dbengine_implicit">Connectionless Execution, Implicit Execution</a>.
</p>



            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="sql_selecting"></a>
    
    <div class="sectionL2">

    <h3>Selecting</h3>
    
    

<p>We began with inserts just so that our test database had some data in it.  The more interesting part of the data is selecting it !  We'll cover UPDATE and DELETE statements later.  The primary construct used to generate SELECT statements is the <code>select()</code> function:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">from </span><span class="python_name">sqlalchemy</span><span class="python_operator">.</span><span class="python_name">sql </span><span class="python_keyword">import </span><span class="python_name">select</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">s </span><span class="python_operator">= </span><span class="python_name">select</span><span class="python_enclosure">([</span><span class="python_name">users</span><span class="python_enclosure">])</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">result </span><span class="python_operator">= </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">s</span><span class="python_enclosure">)</span><span class="python_operator"></span></pre><div class="codepop">SELECT users.id, users.name, users.fullname <br/>
FROM users<br/>
[]</div><pre>

<span class="python_operator"></span></pre>
    </div>
<p>Above, we issued a basic <code>select()</code> call, placing the <code>users</code> table within the COLUMNS clause of the select, and then executing.  SQLAlchemy expanded the <code>users</code> table into the set of each of its columns, and also generated a FROM clause for us.  The result returned is again a <code>ResultProxy</code> object, which acts much like a DBAPI cursor, including methods such as <code>fetchone()</code> and <code>fetchall()</code>.  The easiest way to get rows from it is to just iterate:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">for </span><span class="python_name">row </span><span class="python_keyword">in </span><span class="python_name">result</span><span class="python_operator">:
...     </span><span class="python_keyword">print </span><span class="python_name">row</span><span class="python_operator">
</span><span class="python_enclosure">(</span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">u'jack'</span><span class="python_operator">, </span><span class="python_literal">u'Jack Jones'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_enclosure">(</span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_literal">u'wendy'</span><span class="python_operator">, </span><span class="python_literal">u'Wendy Williams'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_enclosure">(</span><span class="python_number">3</span><span class="python_operator">, </span><span class="python_literal">u'fred'</span><span class="python_operator">, </span><span class="python_literal">u'Fred Flintstone'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_enclosure">(</span><span class="python_number">4</span><span class="python_operator">, </span><span class="python_literal">u'mary'</span><span class="python_operator">, </span><span class="python_literal">u'Mary Contrary'</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>Above, we see that printing each row produces a simple tuple-like result.  We have more options at accessing the data in each row.  One very common way is through dictionary access, using the string names of columns:
</p>

    

    <div class="code">
        <pre>
<span class="python_literal"><a href="javascript:togglePopbox('popbox_4', 'show', 'hide')" class="codepoplink">sql</a>&gt;&gt;&gt; </span><span class="python_name">result </span><span class="python_operator">= </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">s</span><span class="python_enclosure">)</span><span class="python_operator"></span></pre><div id="popbox_4_div" class="codepop" style="display:none;">SELECT users.id, users.name, users.fullname <br/>
FROM users<br/>
[]</div><pre><span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">row </span><span class="python_operator">= </span><span class="python_name">result</span><span class="python_operator">.</span><span class="python_name">fetchone</span><span class="python_enclosure">()</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_literal">"name:"</span><span class="python_operator">, </span><span class="python_name">row</span><span class="python_enclosure">[</span><span class="python_literal">'name'</span><span class="python_enclosure">]</span><span class="python_operator">, </span><span class="python_literal">"; fullname:"</span><span class="python_operator">, </span><span class="python_name">row</span><span class="python_enclosure">[</span><span class="python_literal">'fullname'</span><span class="python_enclosure">]</span><span class="python_operator">
</span><span class="python_name">name</span><span class="python_operator">: </span><span class="python_name">jack </span><span class="python_operator">; </span><span class="python_name">fullname</span><span class="python_operator">: </span><span class="python_name">Jack Jones</span><span class="python_operator">
</span></pre>
    </div>
<p>Integer indexes work as well:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">row </span><span class="python_operator">= </span><span class="python_name">result</span><span class="python_operator">.</span><span class="python_name">fetchone</span><span class="python_enclosure">()</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_literal">"name:"</span><span class="python_operator">, </span><span class="python_name">row</span><span class="python_enclosure">[</span><span class="python_number">1</span><span class="python_enclosure">]</span><span class="python_operator">, </span><span class="python_literal">"; fullname:"</span><span class="python_operator">, </span><span class="python_name">row</span><span class="python_enclosure">[</span><span class="python_number">2</span><span class="python_enclosure">]</span><span class="python_operator">
</span><span class="python_name">name</span><span class="python_operator">: </span><span class="python_name">wendy </span><span class="python_operator">; </span><span class="python_name">fullname</span><span class="python_operator">: </span><span class="python_name">Wendy Williams</span><span class="python_operator">
</span></pre>
    </div>
<p>But another way, whose usefulness will become apparent later on, is to use the <code>Column</code> objects directly as keys:
</p>

    

    <div class="code">
        <pre>
<span class="python_literal"><a href="javascript:togglePopbox('popbox_5', 'show', 'hide')" class="codepoplink">sql</a>&gt;&gt;&gt; </span><span class="python_keyword">for </span><span class="python_name">row </span><span class="python_keyword">in </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">s</span><span class="python_enclosure">)</span><span class="python_operator">:
...     </span><span class="python_keyword">print </span><span class="python_literal">"name:"</span><span class="python_operator">, </span><span class="python_name">row</span><span class="python_enclosure">[</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">name</span><span class="python_enclosure">]</span><span class="python_operator">, </span><span class="python_literal">"; fullname:"</span><span class="python_operator">, </span><span class="python_name">row</span><span class="python_enclosure">[</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">fullname</span><span class="python_enclosure">]</span><span class="python_operator"></span></pre><div id="popbox_5_div" class="codepop" style="display:none;">SELECT users.id, users.name, users.fullname <br/>
FROM users<br/>
[]</div><pre><span class="python_name">name</span><span class="python_operator">: </span><span class="python_name">jack </span><span class="python_operator">; </span><span class="python_name">fullname</span><span class="python_operator">: </span><span class="python_name">Jack Jones</span><span class="python_operator">
</span><span class="python_name">name</span><span class="python_operator">: </span><span class="python_name">wendy </span><span class="python_operator">; </span><span class="python_name">fullname</span><span class="python_operator">: </span><span class="python_name">Wendy Williams</span><span class="python_operator">
</span><span class="python_name">name</span><span class="python_operator">: </span><span class="python_name">fred </span><span class="python_operator">; </span><span class="python_name">fullname</span><span class="python_operator">: </span><span class="python_name">Fred Flintstone</span><span class="python_operator">
</span><span class="python_name">name</span><span class="python_operator">: </span><span class="python_name">mary </span><span class="python_operator">; </span><span class="python_name">fullname</span><span class="python_operator">: </span><span class="python_name">Mary Contrary</span><span class="python_operator">
</span></pre>
    </div>
<p>Result sets which have pending rows remaining should be explicitly closed before discarding.  While the resources referenced by the <code>ResultProxy</code> will be closed when the object is garbage collected, it's better to make it explicit as some database APIs are very picky about such things:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">result</span><span class="python_operator">.</span><span class="python_name">close</span><span class="python_enclosure">()</span><span class="python_operator">
</span></pre>
    </div>
<p>If we'd like to more carefully control the columns which are placed in the COLUMNS clause of the select, we reference individual <code>Column</code> objects from our <code>Table</code>.  These are available as named attributes off the <code>c</code> attribute of the <code>Table</code> object:
</p>

    

    <div class="code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">s </span><span class="python_operator">= </span><span class="python_name">select</span><span class="python_enclosure">([</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">name</span><span class="python_operator">, </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">fullname</span><span class="python_enclosure">])</span><span class="python_operator">
</span><span class="python_literal"><a href="javascript:togglePopbox('popbox_6', 'show', 'hide')" class="codepoplink">sql</a>&gt;&gt;&gt; </span><span class="python_name">result </span><span class="python_operator">= </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">s</span><span class="python_enclosure">)</span><span class="python_operator"></span></pre><div id="popbox_6_div" class="codepop" style="display:none;">SELECT users.name, users.fullname <br/>
FROM users<br/>
[]</div><pre><span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">for </span><span class="python_name">row </span><span class="python_keyword">in </span><span class="python_name">result</span><span class="python_operator">:  
...     </span><span class="python_keyword">print </span><span class="python_name">row</span><span class="python_operator">
</span><span class="python_enclosure">(</span><span class="python_literal">u'jack'</span><span class="python_operator">, </span><span class="python_literal">u'Jack Jones'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_enclosure">(</span><span class="python_literal">u'wendy'</span><span class="python_operator">, </span><span class="python_literal">u'Wendy Williams'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_enclosure">(</span><span class="python_literal">u'fred'</span><span class="python_operator">, </span><span class="python_literal">u'Fred Flintstone'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_enclosure">(</span><span class="python_literal">u'mary'</span><span class="python_operator">, </span><span class="python_literal">u'Mary Contrary'</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>Lets observe something interesting about the FROM clause.  Whereas the generated statement contains two distinct sections, a "SELECT columns" part and a "FROM table" part, our <code>select()</code> construct only has a list containing columns.  How does this work ?  Let's try putting <em>two</em> tables into our <code>select()</code> statement:
</p>

    

    <div class="code">
        <pre>
<span class="python_literal"><a href="javascript:togglePopbox('popbox_7', 'show', 'hide')" class="codepoplink">sql</a>&gt;&gt;&gt; </span><span class="python_keyword">for </span><span class="python_name">row </span><span class="python_keyword">in </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">select</span><span class="python_enclosure">([</span><span class="python_name">users</span><span class="python_operator">, </span><span class="python_name">addresses</span><span class="python_enclosure">]))</span><span class="python_operator">:
...     </span><span class="python_keyword">print </span><span class="python_name">row</span><span class="python_operator"></span></pre><div id="popbox_7_div" class="codepop" style="display:none;">SELECT users.id, users.name, users.fullname, addresses.id, addresses.user_id, addresses.email_address <br/>
FROM users, addresses<br/>
[]</div><pre><span class="python_enclosure">(</span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">u'jack'</span><span class="python_operator">, </span><span class="python_literal">u'Jack Jones'</span><span class="python_operator">, </span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">u'jack@yahoo.com'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_enclosure">(</span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">u'jack'</span><span class="python_operator">, </span><span class="python_literal">u'Jack Jones'</span><span class="python_operator">, </span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">u'jack@msn.com'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_enclosure">(</span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">u'jack'</span><span class="python_operator">, </span><span class="python_literal">u'Jack Jones'</span><span class="python_operator">, </span><span class="python_number">3</span><span class="python_operator">, </span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_literal">u'www@www.org'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_enclosure">(</span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">u'jack'</span><span class="python_operator">, </span><span class="python_literal">u'Jack Jones'</span><span class="python_operator">, </span><span class="python_number">4</span><span class="python_operator">, </span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_literal">u'wendy@aol.com'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_enclosure">(</span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_literal">u'wendy'</span><span class="python_operator">, </span><span class="python_literal">u'Wendy Williams'</span><span class="python_operator">, </span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">u'jack@yahoo.com'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_enclosure">(</span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_literal">u'wendy'</span><span class="python_operator">, </span><span class="python_literal">u'Wendy Williams'</span><span class="python_operator">, </span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">u'jack@msn.com'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_enclosure">(</span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_literal">u'wendy'</span><span class="python_operator">, </span><span class="python_literal">u'Wendy Williams'</span><span class="python_operator">, </span><span class="python_number">3</span><span class="python_operator">, </span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_literal">u'www@www.org'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_enclosure">(</span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_literal">u'wendy'</span><span class="python_operator">, </span><span class="python_literal">u'Wendy Williams'</span><span class="python_operator">, </span><span class="python_number">4</span><span class="python_operator">, </span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_literal">u'wendy@aol.com'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_enclosure">(</span><span class="python_number">3</span><span class="python_operator">, </span><span class="python_literal">u'fred'</span><span class="python_operator">, </span><span class="python_literal">u'Fred Flintstone'</span><span class="python_operator">, </span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">u'jack@yahoo.com'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_enclosure">(</span><span class="python_number">3</span><span class="python_operator">, </span><span class="python_literal">u'fred'</span><span class="python_operator">, </span><span class="python_literal">u'Fred Flintstone'</span><span class="python_operator">, </span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">u'jack@msn.com'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_enclosure">(</span><span class="python_number">3</span><span class="python_operator">, </span><span class="python_literal">u'fred'</span><span class="python_operator">, </span><span class="python_literal">u'Fred Flintstone'</span><span class="python_operator">, </span><span class="python_number">3</span><span class="python_operator">, </span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_literal">u'www@www.org'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_enclosure">(</span><span class="python_number">3</span><span class="python_operator">, </span><span class="python_literal">u'fred'</span><span class="python_operator">, </span><span class="python_literal">u'Fred Flintstone'</span><span class="python_operator">, </span><span class="python_number">4</span><span class="python_operator">, </span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_literal">u'wendy@aol.com'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_enclosure">(</span><span class="python_number">4</span><span class="python_operator">, </span><span class="python_literal">u'mary'</span><span class="python_operator">, </span><span class="python_literal">u'Mary Contrary'</span><span class="python_operator">, </span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">u'jack@yahoo.com'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_enclosure">(</span><span class="python_number">4</span><span class="python_operator">, </span><span class="python_literal">u'mary'</span><span class="python_operator">, </span><span class="python_literal">u'Mary Contrary'</span><span class="python_operator">, </span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">u'jack@msn.com'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_enclosure">(</span><span class="python_number">4</span><span class="python_operator">, </span><span class="python_literal">u'mary'</span><span class="python_operator">, </span><span class="python_literal">u'Mary Contrary'</span><span class="python_operator">, </span><span class="python_number">3</span><span class="python_operator">, </span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_literal">u'www@www.org'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_enclosure">(</span><span class="python_number">4</span><span class="python_operator">, </span><span class="python_literal">u'mary'</span><span class="python_operator">, </span><span class="python_literal">u'Mary Contrary'</span><span class="python_operator">, </span><span class="python_number">4</span><span class="python_operator">, </span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_literal">u'wendy@aol.com'</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>It placed <strong>both</strong> tables into the FROM clause.  But also, it made a real mess.  Those who are familiar with SQL joins know that this is a <strong>Cartesian product</strong>; each row from the <code>users</code> table is produced against each row from the <code>addresses</code> table.  So to put some sanity into this statement, we need a WHERE clause.  Which brings us to the second argument of <code>select()</code>:
</p>

    

    <div class="code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">s </span><span class="python_operator">= </span><span class="python_name">select</span><span class="python_enclosure">([</span><span class="python_name">users</span><span class="python_operator">, </span><span class="python_name">addresses</span><span class="python_enclosure">]</span><span class="python_operator">, </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_operator">==</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_literal"><a href="javascript:togglePopbox('popbox_8', 'show', 'hide')" class="codepoplink">sql</a>&gt;&gt;&gt; </span><span class="python_keyword">for </span><span class="python_name">row </span><span class="python_keyword">in </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">s</span><span class="python_enclosure">)</span><span class="python_operator">:
...     </span><span class="python_keyword">print </span><span class="python_name">row</span><span class="python_operator"></span></pre><div id="popbox_8_div" class="codepop" style="display:none;">SELECT users.id, users.name, users.fullname, addresses.id, addresses.user_id, addresses.email_address <br/>
FROM users, addresses <br/>
WHERE users.id = addresses.user_id<br/>
[]</div><pre><span class="python_enclosure">(</span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">u'jack'</span><span class="python_operator">, </span><span class="python_literal">u'Jack Jones'</span><span class="python_operator">, </span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">u'jack@yahoo.com'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_enclosure">(</span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">u'jack'</span><span class="python_operator">, </span><span class="python_literal">u'Jack Jones'</span><span class="python_operator">, </span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">u'jack@msn.com'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_enclosure">(</span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_literal">u'wendy'</span><span class="python_operator">, </span><span class="python_literal">u'Wendy Williams'</span><span class="python_operator">, </span><span class="python_number">3</span><span class="python_operator">, </span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_literal">u'www@www.org'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_enclosure">(</span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_literal">u'wendy'</span><span class="python_operator">, </span><span class="python_literal">u'Wendy Williams'</span><span class="python_operator">, </span><span class="python_number">4</span><span class="python_operator">, </span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_literal">u'wendy@aol.com'</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>So that looks a lot better, we added an expression to our <code>select()</code> which had the effect of adding <code>WHERE users.id = addresses.user_id</code> to our statement, and our results were managed down so that the join of <code>users</code> and <code>addresses</code> rows made sense.  But let's look at that expression?  It's using just a Python equality operator between two different <code>Column</code> objects.  It should be clear that something is up.  Saying <code>1==1</code> produces <code>True</code>, and <code>1==2</code> produces <code>False</code>, not a WHERE clause.  So lets see exactly what that expression is doing:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_operator">==</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id </span><span class="python_operator">
&lt;</span><span class="python_name">sqlalchemy</span><span class="python_operator">.</span><span class="python_name">sql</span><span class="python_operator">.</span><span class="python_name">expression</span><span class="python_operator">.</span><span class="python_name">_BinaryExpression object at </span><span class="python_number">0x</span><span class="python_operator">...&gt;
</span></pre>
    </div>
<p>Wow, surprise !  This is neither a <code>True</code> nor a <code>False</code>.  Well what is it ?
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">str</span><span class="python_enclosure">(</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_operator">==</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_literal">'users.id = addresses.user_id'</span><span class="python_operator">
</span></pre>
    </div>
<p>As you can see, the <code>==</code> operator is producing an object that is very much like the <code>Insert</code> and <code>select()</code> objects we've made so far, thanks to Python's <code>__eq__()</code> builtin; you call <code>str()</code> on it and it produces SQL.  By now, one can that everything we are working with is ultimately the same type of object.  SQLAlchemy terms the base class of all of these expressions as <code>sqlalchemy.sql.ClauseElement</code>.
</p>



            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="sql_operators"></a>
    
    <div class="sectionL2">

    <h3>Operators</h3>
    
    

<p>Since we've stumbled upon SQLAlchemy's operator paradigm, let's go through some of its capabilities.  We've seen how to equate two columns to each other:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_operator">==</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">
</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">id </span><span class="python_operator">= </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">
</span></pre>
    </div>
<p>If we use a literal value (a literal meaning, not a SQLAlchemy clause object), we get a bind parameter:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_operator">==</span><span class="python_number">7</span><span class="python_operator">
</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">id </span><span class="python_operator">= :</span><span class="python_name">users_id_1</span><span class="python_operator">
</span></pre>
    </div>
<p>The <code>7</code> literal is embedded in <code>ClauseElement</code>; we can use the same trick we did with the <code>Insert</code> object to see it:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_enclosure">(</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_operator">==</span><span class="python_number">7</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">compile</span><span class="python_enclosure">()</span><span class="python_operator">.</span><span class="python_name">params</span><span class="python_operator">
</span><span class="python_enclosure">{</span><span class="python_literal">'users_id_1'</span><span class="python_operator">: </span><span class="python_number">7</span><span class="python_enclosure">}</span><span class="python_operator">
</span></pre>
    </div>
<p>Most Python operators, as it turns out, produce a SQL expression here, like equals, not equals, etc.:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id </span><span class="python_operator">!= </span><span class="python_number">7</span><span class="python_operator">
</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">id </span><span class="python_operator">!= :</span><span class="python_name">users_id_1</span><span class="python_operator">
</span>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_comment"># None converts to IS NULL</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">name </span><span class="python_operator">== </span><span class="python_name">None</span><span class="python_operator">
</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">name IS NULL</span><span class="python_operator">
</span>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_comment"># reverse works too </span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_literal">'fred' </span><span class="python_operator">&gt; </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">name</span><span class="python_operator">
</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">name </span><span class="python_operator">&lt; :</span><span class="python_name">users_name_1</span><span class="python_operator">
</span></pre>
    </div>
<p>If we add two integer columns together, we get an addition expression:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id </span><span class="python_operator">+ </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_operator">
</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">id </span><span class="python_operator">+ </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_operator">
</span></pre>
    </div>
<p>Interestingly, the type of the <code>Column</code> is important !  If we use <code>+</code> with two string based columns (recall we put types like <code>Integer</code> and <code>String</code> on our <code>Column</code> objects at the beginning), we get something different:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">name </span><span class="python_operator">+ </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">fullname</span><span class="python_operator">
</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">name </span><span class="python_operator">|| </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">fullname</span><span class="python_operator">
</span></pre>
    </div>
<p>Where <code>||</code> is the string concatenation operator used on most databases.  But not all of them.  MySQL users, fear not:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_enclosure">(</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">name </span><span class="python_operator">+ </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">fullname</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">compile</span><span class="python_enclosure">(</span><span class="python_name">bind</span><span class="python_operator">=</span><span class="python_name">create_engine</span><span class="python_enclosure">(</span><span class="python_literal">'mysql://'</span><span class="python_enclosure">))</span><span class="python_operator">
</span><span class="python_name">concat</span><span class="python_enclosure">(</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">name</span><span class="python_operator">, </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">fullname</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>The above illustrates the SQL that's generated for an <code>Engine</code> that's connected to a MySQL database; the <code>||</code> operator now compiles as MySQL's <code>concat()</code> function.
</p>
<p>If you have come across an operator which really isn't available, you can always use the <code>op()</code> method; this generates whatever operator you need:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">name</span><span class="python_operator">.</span><span class="python_name">op</span><span class="python_enclosure">(</span><span class="python_literal">'tiddlywinks'</span><span class="python_enclosure">)(</span><span class="python_literal">'foo'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">name tiddlywinks </span><span class="python_operator">:</span><span class="python_name">users_name_1</span><span class="python_operator">
</span></pre>
    </div>



            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="sql_conjunctions"></a>
    
    <div class="sectionL2">

    <h3>Conjunctions</h3>
    
    

<p>We'd like to show off some of our operators inside of <code>select()</code> constructs.  But we need to lump them together a little more, so let's first introduce some conjunctions.  Conjunctions are those little words like AND and OR that put things together.  We'll also hit upon NOT.  AND, OR and NOT can work from the corresponding functions SQLAlchemy provides (notice we also throw in a LIKE):
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">from </span><span class="python_name">sqlalchemy</span><span class="python_operator">.</span><span class="python_name">sql </span><span class="python_keyword">import </span><span class="python_name">and_</span><span class="python_operator">, </span><span class="python_name">or_</span><span class="python_operator">, </span><span class="python_name">not_</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">and_</span><span class="python_enclosure">(</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">name</span><span class="python_operator">.</span><span class="python_name">like</span><span class="python_enclosure">(</span><span class="python_literal">'j%'</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_operator">==</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">, </span>
<span class="python_operator">...     </span><span class="python_name">or_</span><span class="python_enclosure">(</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">email_address</span><span class="python_operator">==</span><span class="python_literal">'wendy@aol.com'</span><span class="python_operator">, </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">email_address</span><span class="python_operator">==</span><span class="python_literal">'jack@yahoo.com'</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
<span class="python_operator">...     </span><span class="python_name">not_</span><span class="python_enclosure">(</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_operator">&gt;</span><span class="python_number">5</span><span class="python_enclosure">))</span><span class="python_operator">
</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">name LIKE </span><span class="python_operator">:</span><span class="python_name">users_name_1 AND users</span><span class="python_operator">.</span><span class="python_name">id </span><span class="python_operator">= </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">user_id AND </span><span class="python_operator">
</span><span class="python_enclosure">(</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">email_address </span><span class="python_operator">= :</span><span class="python_name">addresses_email_address_1 OR addresses</span><span class="python_operator">.</span><span class="python_name">email_address </span><span class="python_operator">= :</span><span class="python_name">addresses_email_address_2</span><span class="python_enclosure">) </span><span class="python_operator">
</span><span class="python_name">AND users</span><span class="python_operator">.</span><span class="python_name">id </span><span class="python_operator">&lt;= :</span><span class="python_name">users_id_1</span><span class="python_operator">
</span></pre>
    </div>
<p>And you can also use the re-jiggered bitwise AND, OR and NOT operators, although because of Python operator precedence you have to watch your parenthesis:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">name</span><span class="python_operator">.</span><span class="python_name">like</span><span class="python_enclosure">(</span><span class="python_literal">'j%'</span><span class="python_enclosure">) </span><span class="python_operator">&amp; </span><span class="python_enclosure">(</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_operator">==</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_enclosure">) </span><span class="python_operator">&amp;  \
...     </span><span class="python_enclosure">((</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">email_address</span><span class="python_operator">==</span><span class="python_literal">'wendy@aol.com'</span><span class="python_enclosure">) </span><span class="python_operator">| </span><span class="python_enclosure">(</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">email_address</span><span class="python_operator">==</span><span class="python_literal">'jack@yahoo.com'</span><span class="python_enclosure">))</span><span class="python_operator"> \
...     &amp; ~</span><span class="python_enclosure">(</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_operator">&gt;</span><span class="python_number">5</span><span class="python_enclosure">) </span><span class="python_operator">
</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">name LIKE </span><span class="python_operator">:</span><span class="python_name">users_name_1 AND users</span><span class="python_operator">.</span><span class="python_name">id </span><span class="python_operator">= </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">user_id AND </span><span class="python_operator">
</span><span class="python_enclosure">(</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">email_address </span><span class="python_operator">= :</span><span class="python_name">addresses_email_address_1 OR addresses</span><span class="python_operator">.</span><span class="python_name">email_address </span><span class="python_operator">= :</span><span class="python_name">addresses_email_address_2</span><span class="python_enclosure">) </span><span class="python_operator">
</span><span class="python_name">AND users</span><span class="python_operator">.</span><span class="python_name">id </span><span class="python_operator">&lt;= :</span><span class="python_name">users_id_1</span><span class="python_operator">
</span></pre>
    </div>
<p>So with all of this vocabulary, let's select all users who have an email address at AOL or MSN, whose name starts with a letter between "m" and "z", and we'll also generate a column containing their full name combined with their email address.  We will add two new constructs to this statement, <code>between()</code> and <code>label()</code>.  <code>between()</code> produces a BETWEEN clause, and <code>label()</code> is used in a column expression to produce labels using the <code>AS</code> keyword; it's recommended when selecting from expressions that otherwise would not have a name:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">s </span><span class="python_operator">= </span><span class="python_name">select</span><span class="python_enclosure">([(</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">fullname </span><span class="python_operator">+ </span><span class="python_literal">", " </span><span class="python_operator">+ </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">email_address</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">label</span><span class="python_enclosure">(</span><span class="python_literal">'title'</span><span class="python_enclosure">)]</span><span class="python_operator">, </span>
<span class="python_operator">...        </span><span class="python_name">and_</span><span class="python_enclosure">( </span>
<span class="python_operator">...            </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_operator">==</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">, </span>
<span class="python_operator">...            </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">name</span><span class="python_operator">.</span><span class="python_name">between</span><span class="python_enclosure">(</span><span class="python_literal">'m'</span><span class="python_operator">, </span><span class="python_literal">'z'</span><span class="python_enclosure">)</span><span class="python_operator">, </span>
<span class="python_operator">...           </span><span class="python_name">or_</span><span class="python_enclosure">(</span>
<span class="python_operator">...              </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">email_address</span><span class="python_operator">.</span><span class="python_name">like</span><span class="python_enclosure">(</span><span class="python_literal">'%@aol.com'</span><span class="python_enclosure">)</span><span class="python_operator">, </span>
<span class="python_operator">...              </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">email_address</span><span class="python_operator">.</span><span class="python_name">like</span><span class="python_enclosure">(</span><span class="python_literal">'%@msn.com'</span><span class="python_enclosure">)</span>
<span class="python_operator">...           </span><span class="python_enclosure">)</span>
<span class="python_operator">...        </span><span class="python_enclosure">)</span>
<span class="python_operator">...    </span><span class="python_enclosure">) </span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">s</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">fetchall</span><span class="python_enclosure">() </span><span class="python_operator">
</span><span class="python_name">SELECT users</span><span class="python_operator">.</span><span class="python_name">fullname </span><span class="python_operator">|| ? || </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">email_address AS title </span><span class="python_operator">
</span><span class="python_name">FROM users</span><span class="python_operator">, </span><span class="python_name">addresses </span><span class="python_operator">
</span><span class="python_name">WHERE users</span><span class="python_operator">.</span><span class="python_name">id </span><span class="python_operator">= </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">user_id AND users</span><span class="python_operator">.</span><span class="python_name">name BETWEEN</span><span class="python_operator"> ? </span><span class="python_name">AND</span><span class="python_operator"> ? </span><span class="python_name">AND </span><span class="python_operator">
</span><span class="python_enclosure">(</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">email_address LIKE</span><span class="python_operator"> ? </span><span class="python_name">OR addresses</span><span class="python_operator">.</span><span class="python_name">email_address LIKE</span><span class="python_operator"> ?</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_enclosure">[</span><span class="python_literal">', '</span><span class="python_operator">, </span><span class="python_literal">'m'</span><span class="python_operator">, </span><span class="python_literal">'z'</span><span class="python_operator">, </span><span class="python_literal">'%@aol.com'</span><span class="python_operator">, </span><span class="python_literal">'%@msn.com'</span><span class="python_enclosure">]</span><span class="python_operator">
</span><span class="python_enclosure">[(</span><span class="python_literal">u'Wendy Williams, wendy@aol.com'</span><span class="python_operator">,</span><span class="python_enclosure">)]</span><span class="python_operator">
</span></pre>
    </div>
<p>Once again, SQLAlchemy figured out the FROM clause for our statement.  In fact it will determine the FROM clause based on all of its other bits; the columns clause, the whereclause, and also some other elements which we haven't covered yet, which include ORDER BY, GROUP BY, and HAVING. 
</p>



            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="sql_text"></a>
    
    <div class="sectionL2">

    <h3>Using Text</h3>
    
    

<p>Our last example really became a handful to type.  Going from what one understands to be a textual SQL expression into a Python construct which groups components together in a programmatic style can be hard.  That's why SQLAlchemy lets you just use strings too.  The <code>text()</code> construct represents any textual statement.  To use bind parameters with <code>text()</code>, always use the named colon format.  Such as below, we create a <code>text()</code> and execute it, feeding in the bind parameters to the <code>execute()</code> method:
</p>

    

    <div class="code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">from </span><span class="python_name">sqlalchemy</span><span class="python_operator">.</span><span class="python_name">sql </span><span class="python_keyword">import </span><span class="python_name">text</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">s </span><span class="python_operator">= </span><span class="python_name">text</span><span class="python_enclosure">(</span><span class="python_literal">"""SELECT users.fullname || ', ' || addresses.email_address AS title 
...            FROM users, addresses 
...            WHERE users.id = addresses.user_id AND users.name BETWEEN :x AND :y AND 
...            (addresses.email_address LIKE :e1 OR addresses.email_address LIKE :e2)
...        """</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_literal"><a href="javascript:togglePopbox('popbox_9', 'show', 'hide')" class="codepoplink">sql</a>&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">s</span><span class="python_operator">, </span><span class="python_name">x</span><span class="python_operator">=</span><span class="python_literal">'m'</span><span class="python_operator">, </span><span class="python_name">y</span><span class="python_operator">=</span><span class="python_literal">'z'</span><span class="python_operator">, </span><span class="python_name">e1</span><span class="python_operator">=</span><span class="python_literal">'%@aol.com'</span><span class="python_operator">, </span><span class="python_name">e2</span><span class="python_operator">=</span><span class="python_literal">'%@msn.com'</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">fetchall</span><span class="python_enclosure">()</span><span class="python_operator"> </span></pre><div id="popbox_9_div" class="codepop" style="display:none;">SELECT users.fullname || ', ' || addresses.email_address AS title <br/>
FROM users, addresses <br/>
WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND <br/>
(addresses.email_address LIKE ? OR addresses.email_address LIKE ?)<br/>
['m', 'z', '%@aol.com', '%@msn.com']</div><pre><span class="python_enclosure">[(</span><span class="python_literal">u'Wendy Williams, wendy@aol.com'</span><span class="python_operator">,</span><span class="python_enclosure">)]</span><span class="python_operator">
</span></pre>
    </div>
<p>To gain a "hybrid" approach, any of SA's SQL constructs can have text freely intermingled wherever you like - the <code>text()</code> construct can be placed within any other <code>ClauseElement</code> construct, and when used in a non-operator context, a direct string may be placed which converts to <code>text()</code> automatically.  Below we combine the usage of <code>text()</code> and strings with our constructed <code>select()</code> object, by using the <code>select()</code> object to structure the statement, and the <code>text()</code>/strings to provide all the content within the structure.  For this example, SQLAlchemy is not given any <code>Column</code> or <code>Table</code> objects in any of its expressions, so it cannot generate a FROM clause.  So we also give it the <code>from_obj</code> keyword argument, which is a list of <code>ClauseElements</code> (or strings) to be placed within the FROM clause:
</p>

    

    <div class="code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">s </span><span class="python_operator">= </span><span class="python_name">select</span><span class="python_enclosure">([</span><span class="python_name">text</span><span class="python_enclosure">(</span><span class="python_literal">"users.fullname || ', ' || addresses.email_address AS title"</span><span class="python_enclosure">)]</span><span class="python_operator">, </span>
<span class="python_operator">...        </span><span class="python_name">and_</span><span class="python_enclosure">( </span>
<span class="python_operator">...            </span><span class="python_literal">"users.id = addresses.user_id"</span><span class="python_operator">, </span>
<span class="python_operator">...             </span><span class="python_literal">"users.name BETWEEN 'm' AND 'z'"</span><span class="python_operator">,</span>
<span class="python_operator">...             </span><span class="python_literal">"(addresses.email_address LIKE :x OR addresses.email_address LIKE :y)"</span>
<span class="python_operator">...        </span><span class="python_enclosure">)</span><span class="python_operator">,</span>
<span class="python_operator">...         </span><span class="python_name">from_obj</span><span class="python_operator">=</span><span class="python_enclosure">[</span><span class="python_literal">'users'</span><span class="python_operator">, </span><span class="python_literal">'addresses'</span><span class="python_enclosure">]</span>
<span class="python_operator">...    </span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_literal"><a href="javascript:togglePopbox('popbox_10', 'show', 'hide')" class="codepoplink">sql</a>&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">s</span><span class="python_operator">, </span><span class="python_name">x</span><span class="python_operator">=</span><span class="python_literal">'%@aol.com'</span><span class="python_operator">, </span><span class="python_name">y</span><span class="python_operator">=</span><span class="python_literal">'%@msn.com'</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">fetchall</span><span class="python_enclosure">()</span><span class="python_operator"> </span></pre><div id="popbox_10_div" class="codepop" style="display:none;">SELECT users.fullname || ', ' || addresses.email_address AS title <br/>
FROM users, addresses <br/>
WHERE users.id = addresses.user_id AND users.name BETWEEN 'm' AND 'z' AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?)<br/>
['%@aol.com', '%@msn.com']</div><pre><span class="python_enclosure">[(</span><span class="python_literal">u'Wendy Williams, wendy@aol.com'</span><span class="python_operator">,</span><span class="python_enclosure">)]</span><span class="python_operator">
</span></pre>
    </div>
<p>Going from constructed SQL to text, we lose some capabilities.  We lose the capability for SQLAlchemy to compile our expression to a specific target database; above, our expression won't work with MySQL since it has no <code>||</code> construct.  It also becomes more tedious for SQLAlchemy to be made aware of the datatypes in use; for example, if our bind parameters required UTF-8 encoding before going in, or conversion from a Python <code>datetime</code> into a string (as is required with SQLite), we would have to add extra information to our <code>text()</code> construct.  Similar issues arise on the result set side, where SQLAlchemy also performs type-specific data conversion in some cases; still more information can be added to <code>text()</code> to work around this.  But what we really lose from our statement is the ability to manipulate it, transform it, and analyze it.  These features are critical when using the ORM, which makes heavy usage of relational transformations.  To show off what we mean, we'll first introduce the ALIAS construct and the JOIN construct, just so we have some juicier bits to play with.
</p>



            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="sql_aliases"></a>
    
    <div class="sectionL2">

    <h3>Using Aliases</h3>
    
    

<p>The alias corresponds to a "renamed" version of a table or arbitrary relation, which occurs anytime you say "SELECT  .. FROM sometable AS someothername".  The <code>AS</code> creates a new name for the table.  Aliases are super important in SQL as they allow you to reference the same table more than once.  Scenarios where you need to do this include when you self-join a table to itself, or more commonly when you need to join from a parent table to a child table multiple times.  For example, we know that our user <code>jack</code> has two email addresses.  How can we locate jack based on the combination of those two addresses?  We need to join twice to it.  Let's construct two distinct aliases for the <code>addresses</code> table and join:
</p>

    

    <div class="code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">a1 </span><span class="python_operator">= </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">alias</span><span class="python_enclosure">(</span><span class="python_literal">'a1'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">a2 </span><span class="python_operator">= </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">alias</span><span class="python_enclosure">(</span><span class="python_literal">'a2'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">s </span><span class="python_operator">= </span><span class="python_name">select</span><span class="python_enclosure">([</span><span class="python_name">users</span><span class="python_enclosure">]</span><span class="python_operator">, </span><span class="python_name">and_</span><span class="python_enclosure">(</span>
<span class="python_operator">...        </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_operator">==</span><span class="python_name">a1</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">, </span>
<span class="python_operator">...        </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_operator">==</span><span class="python_name">a2</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">, </span>
<span class="python_operator">...        </span><span class="python_name">a1</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">email_address</span><span class="python_operator">==</span><span class="python_literal">'jack@msn.com'</span><span class="python_operator">, </span>
<span class="python_operator">...        </span><span class="python_name">a2</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">email_address</span><span class="python_operator">==</span><span class="python_literal">'jack@yahoo.com'</span>
<span class="python_operator">...   </span><span class="python_enclosure">))</span><span class="python_operator">
</span><span class="python_literal"><a href="javascript:togglePopbox('popbox_11', 'show', 'hide')" class="codepoplink">sql</a>&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">s</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">fetchall</span><span class="python_enclosure">()</span><span class="python_operator"></span></pre><div id="popbox_11_div" class="codepop" style="display:none;">SELECT users.id, users.name, users.fullname <br/>
FROM users, addresses AS a1, addresses AS a2 <br/>
WHERE users.id = a1.user_id AND users.id = a2.user_id AND a1.email_address = ? AND a2.email_address = ?<br/>
['jack@msn.com', 'jack@yahoo.com']</div><pre><span class="python_enclosure">[(</span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">u'jack'</span><span class="python_operator">, </span><span class="python_literal">u'Jack Jones'</span><span class="python_enclosure">)]</span><span class="python_operator">
</span></pre>
    </div>
<p>Easy enough.  One thing that we're going for with the SQL Expression Language is the melding of programmatic behavior with SQL generation.  Coming up with names like <code>a1</code> and <code>a2</code> is messy; we really didn't need to use those names anywhere, it's just the database that needed them.  Plus, we might write some code that uses alias objects that came from several different places, and it's difficult to ensure that they all have unique names.  So instead, we just let SQLAlchemy make the names for us, using "anonymous" aliases:
</p>

    

    <div class="code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">a1 </span><span class="python_operator">= </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">alias</span><span class="python_enclosure">()</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">a2 </span><span class="python_operator">= </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">alias</span><span class="python_enclosure">()</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">s </span><span class="python_operator">= </span><span class="python_name">select</span><span class="python_enclosure">([</span><span class="python_name">users</span><span class="python_enclosure">]</span><span class="python_operator">, </span><span class="python_name">and_</span><span class="python_enclosure">(</span>
<span class="python_operator">...        </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_operator">==</span><span class="python_name">a1</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">, </span>
<span class="python_operator">...        </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_operator">==</span><span class="python_name">a2</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">, </span>
<span class="python_operator">...        </span><span class="python_name">a1</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">email_address</span><span class="python_operator">==</span><span class="python_literal">'jack@msn.com'</span><span class="python_operator">, </span>
<span class="python_operator">...        </span><span class="python_name">a2</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">email_address</span><span class="python_operator">==</span><span class="python_literal">'jack@yahoo.com'</span>
<span class="python_operator">...   </span><span class="python_enclosure">))</span><span class="python_operator">
</span><span class="python_literal"><a href="javascript:togglePopbox('popbox_12', 'show', 'hide')" class="codepoplink">sql</a>&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">s</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">fetchall</span><span class="python_enclosure">()</span><span class="python_operator"></span></pre><div id="popbox_12_div" class="codepop" style="display:none;">SELECT users.id, users.name, users.fullname <br/>
FROM users, addresses AS addresses_1, addresses AS addresses_2 <br/>
WHERE users.id = addresses_1.user_id AND users.id = addresses_2.user_id AND addresses_1.email_address = ? AND addresses_2.email_address = ?<br/>
['jack@msn.com', 'jack@yahoo.com']</div><pre><span class="python_enclosure">[(</span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">u'jack'</span><span class="python_operator">, </span><span class="python_literal">u'Jack Jones'</span><span class="python_enclosure">)]</span><span class="python_operator">
</span></pre>
    </div>
<p>One super-huge advantage of anonymous aliases is that not only did we not have to guess up a random name, but we can also be guaranteed that the above SQL string is <strong>deterministically</strong> generated to be the same every time.  This is important for databases such as Oracle which cache compiled "query plans" for their statements, and need to see the same SQL string in order to make use of it.
</p>
<p>Aliases can of course be used for anything which you can SELECT from, including SELECT statements themselves.  We can self-join the <code>users</code> table back to the <code>select()</code> we've created by making an alias of the entire statement.  The <code>correlate(None)</code> directive is to avoid SQLAlchemy's attempt to "correlate" the inner <code>users</code> table with the outer one:
</p>

    

    <div class="code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">a1 </span><span class="python_operator">= </span><span class="python_name">s</span><span class="python_operator">.</span><span class="python_name">correlate</span><span class="python_enclosure">(</span><span class="python_name">None</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">alias</span><span class="python_enclosure">()</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">s </span><span class="python_operator">= </span><span class="python_name">select</span><span class="python_enclosure">([</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">name</span><span class="python_enclosure">]</span><span class="python_operator">, </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_operator">==</span><span class="python_name">a1</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_literal"><a href="javascript:togglePopbox('popbox_13', 'show', 'hide')" class="codepoplink">sql</a>&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">s</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">fetchall</span><span class="python_enclosure">()</span><span class="python_operator"></span></pre><div id="popbox_13_div" class="codepop" style="display:none;">SELECT users.name <br/>
FROM users, (SELECT users.id AS id, users.name AS name, users.fullname AS fullname <br/>
FROM users, addresses AS addresses_1, addresses AS addresses_2 <br/>
WHERE users.id = addresses_1.user_id AND users.id = addresses_2.user_id AND addresses_1.email_address = ? AND addresses_2.email_address = ?) AS anon_1 <br/>
WHERE users.id = anon_1.id<br/>
['jack@msn.com', 'jack@yahoo.com']</div><pre><span class="python_enclosure">[(</span><span class="python_literal">u'jack'</span><span class="python_operator">,</span><span class="python_enclosure">)]</span><span class="python_operator">
</span></pre>
    </div>



            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="sql_joins"></a>
    
    <div class="sectionL2">

    <h3>Using Joins</h3>
    
    

<p>We're halfway along to being able to construct any SELECT expression.  The next cornerstone of the SELECT is the JOIN expression.  We've already been doing joins in our examples, by just placing two tables in either the columns clause or the where clause of the <code>select()</code> construct.  But if we want to make a real "JOIN" or "OUTERJOIN" construct, we use the <code>join()</code> and <code>outerjoin()</code> methods, most commonly accessed from the left table in the join:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">join</span><span class="python_enclosure">(</span><span class="python_name">addresses</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_name">users JOIN addresses ON users</span><span class="python_operator">.</span><span class="python_name">id </span><span class="python_operator">= </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">
</span></pre>
    </div>
<p>The alert reader will see more surprises; SQLAlchemy figured out how to JOIN the two tables !  The ON condition of the join, as it's called, was automatically generated based on the <code>ForeignKey</code> object which we placed on the <code>addresses</code> table way at the beginning of this tutorial.  Already the <code>join()</code> construct is looking like a much better way to join tables.
</p>
<p>Of course you can join on whatever expression you want, such as if we want to join on all users who use the same name in their email address as their username:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">join</span><span class="python_enclosure">(</span><span class="python_name">addresses</span><span class="python_operator">, </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">email_address</span><span class="python_operator">.</span><span class="python_name">like</span><span class="python_enclosure">(</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">name </span><span class="python_operator">+ </span><span class="python_literal">'%'</span><span class="python_enclosure">))</span><span class="python_operator">
</span><span class="python_name">users JOIN addresses ON addresses</span><span class="python_operator">.</span><span class="python_name">email_address LIKE users</span><span class="python_operator">.</span><span class="python_name">name </span><span class="python_operator">|| :</span><span class="python_name">users_name_1</span><span class="python_operator">
</span></pre>
    </div>
<p>When we create a <code>select()</code> construct, SQLAlchemy looks around at the tables we've mentioned and then places them in the FROM clause of the statement.  When we use JOINs however, we know what FROM clause we want, so here we make usage of the <code>from_obj</code> keyword argument:
</p>

    

    <div class="code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">s </span><span class="python_operator">= </span><span class="python_name">select</span><span class="python_enclosure">([</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">fullname</span><span class="python_enclosure">]</span><span class="python_operator">, </span><span class="python_name">from_obj</span><span class="python_operator">=</span><span class="python_enclosure">[</span>
<span class="python_operator">...    </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">join</span><span class="python_enclosure">(</span><span class="python_name">addresses</span><span class="python_operator">, </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">email_address</span><span class="python_operator">.</span><span class="python_name">like</span><span class="python_enclosure">(</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">name </span><span class="python_operator">+ </span><span class="python_literal">'%'</span><span class="python_enclosure">))</span>
<span class="python_operator">...    </span><span class="python_enclosure">])</span><span class="python_operator">
</span><span class="python_literal"><a href="javascript:togglePopbox('popbox_14', 'show', 'hide')" class="codepoplink">sql</a>&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">s</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">fetchall</span><span class="python_enclosure">()</span><span class="python_operator"></span></pre><div id="popbox_14_div" class="codepop" style="display:none;">SELECT users.fullname <br/>
FROM users JOIN addresses ON addresses.email_address LIKE users.name || ?<br/>
['%']</div><pre><span class="python_enclosure">[(</span><span class="python_literal">u'Jack Jones'</span><span class="python_operator">,</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_enclosure">(</span><span class="python_literal">u'Jack Jones'</span><span class="python_operator">,</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_enclosure">(</span><span class="python_literal">u'Wendy Williams'</span><span class="python_operator">,</span><span class="python_enclosure">)]</span><span class="python_operator">
</span></pre>
    </div>
<p>The <code>outerjoin()</code> function just creates <code>LEFT OUTER JOIN</code> constructs.  It's used just like <code>join()</code>:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">s </span><span class="python_operator">= </span><span class="python_name">select</span><span class="python_enclosure">([</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">fullname</span><span class="python_enclosure">]</span><span class="python_operator">, </span><span class="python_name">from_obj</span><span class="python_operator">=</span><span class="python_enclosure">[</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">outerjoin</span><span class="python_enclosure">(</span><span class="python_name">addresses</span><span class="python_enclosure">)])</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">s</span><span class="python_operator">
</span><span class="python_name">SELECT users</span><span class="python_operator">.</span><span class="python_name">fullname </span><span class="python_operator">
</span><span class="python_name">FROM users LEFT OUTER JOIN addresses ON users</span><span class="python_operator">.</span><span class="python_name">id </span><span class="python_operator">= </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">
</span></pre>
    </div>
<p>That's the output <code>outerjoin()</code> produces, unless, of course, you're stuck in a gig using Oracle prior to version 9, and you've set up your engine (which would be using <code>OracleDialect</code>) to use Oracle-specific SQL:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">from </span><span class="python_name">sqlalchemy</span><span class="python_operator">.</span><span class="python_name">databases</span><span class="python_operator">.</span><span class="python_name">oracle </span><span class="python_keyword">import </span><span class="python_name">OracleDialect</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">s</span><span class="python_operator">.</span><span class="python_name">compile</span><span class="python_enclosure">(</span><span class="python_name">dialect</span><span class="python_operator">=</span><span class="python_name">OracleDialect</span><span class="python_enclosure">(</span><span class="python_name">use_ansi</span><span class="python_operator">=</span><span class="python_name">False</span><span class="python_enclosure">))</span><span class="python_operator">
</span><span class="python_name">SELECT users</span><span class="python_operator">.</span><span class="python_name">fullname </span><span class="python_operator">
</span><span class="python_name">FROM users</span><span class="python_operator">, </span><span class="python_name">addresses </span><span class="python_operator">
</span><span class="python_name">WHERE users</span><span class="python_operator">.</span><span class="python_name">id </span><span class="python_operator">= </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_enclosure">(</span><span class="python_operator">+</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>If you don't know what that SQL means, don't worry !  The secret tribe of Oracle DBAs don't want their black magic being found out ;).
</p>



            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="sql_transform"></a>
    
    <div class="sectionL2">

    <h3>Intro to Generative Selects and Transformations</h3>
    
    

<p>We've now gained the ability to construct very sophisticated statements.  We can use all kinds of operators, table constructs, text, joins, and aliases.  The point of all of this, as mentioned earlier, is not that it's an "easier" or "better" way to write SQL than just writing a SQL statement yourself; the point is that it's better for writing <em>programmatically generated</em> SQL which can be morphed and adapted as needed in automated scenarios.
</p>
<p>To support this, the <code>select()</code> construct we've been working with supports piecemeal construction, in addition to the "all at once" method we've been doing.  Suppose you're writing a search function, which receives criterion and then must construct a select from it.  To accomplish this, upon each criterion encountered, you apply "generative" criterion to an existing <code>select()</code> construct with new elements, one at a time.  We start with a basic <code>select()</code> constructed with the shortcut method available on the <code>users</code> table:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">query </span><span class="python_operator">= </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">select</span><span class="python_enclosure">()</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">query</span><span class="python_operator">
</span><span class="python_name">SELECT users</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_operator">, </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">name</span><span class="python_operator">, </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">fullname </span><span class="python_operator">
</span><span class="python_name">FROM users</span><span class="python_operator">
</span></pre>
    </div>
<p>We encounter search criterion of "name='jack'".  So we apply WHERE criterion stating such:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">query </span><span class="python_operator">= </span><span class="python_name">query</span><span class="python_operator">.</span><span class="python_name">where</span><span class="python_enclosure">(</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">name</span><span class="python_operator">==</span><span class="python_literal">'jack'</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>Next, we encounter that they'd like the results in descending order by full name.  We apply ORDER BY, using an extra modifier <code>desc</code>:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">query </span><span class="python_operator">= </span><span class="python_name">query</span><span class="python_operator">.</span><span class="python_name">order_by</span><span class="python_enclosure">(</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">fullname</span><span class="python_operator">.</span><span class="python_name">desc</span><span class="python_enclosure">())</span><span class="python_operator">
</span></pre>
    </div>
<p>We also come across that they'd like only users who have an address at MSN.  A quick way to tack this on is by using an EXISTS clause, which we correlate to the <code>users</code> table in the enclosing SELECT:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">from </span><span class="python_name">sqlalchemy</span><span class="python_operator">.</span><span class="python_name">sql </span><span class="python_keyword">import </span><span class="python_name">exists</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">query </span><span class="python_operator">= </span><span class="python_name">query</span><span class="python_operator">.</span><span class="python_name">where</span><span class="python_enclosure">(</span>
<span class="python_operator">...    </span><span class="python_name">exists</span><span class="python_enclosure">([</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_enclosure">]</span><span class="python_operator">, </span>
<span class="python_operator">...        </span><span class="python_name">and_</span><span class="python_enclosure">(</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">==</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_operator">, </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">email_address</span><span class="python_operator">.</span><span class="python_name">like</span><span class="python_enclosure">(</span><span class="python_literal">'%@msn.com'</span><span class="python_enclosure">))</span>
<span class="python_operator">...    </span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">correlate</span><span class="python_enclosure">(</span><span class="python_name">users</span><span class="python_enclosure">))</span><span class="python_operator">
</span></pre>
    </div>
<p>And finally, the application also wants to see the listing of email addresses at once; so to save queries, we outerjoin the <code>addresses</code> table (using an outer join so that users with no addresses come back as well; since we're programmatic, we might not have kept track that we used an EXISTS clause against the <code>addresses</code> table too...).  Additionally, since the <code>users</code> and <code>addresses</code> table both have a column named <code>id</code>, let's isolate their names from each other in the COLUMNS clause by using labels:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">query </span><span class="python_operator">= </span><span class="python_name">query</span><span class="python_operator">.</span><span class="python_name">column</span><span class="python_enclosure">(</span><span class="python_name">addresses</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">select_from</span><span class="python_enclosure">(</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">outerjoin</span><span class="python_enclosure">(</span><span class="python_name">addresses</span><span class="python_enclosure">))</span><span class="python_operator">.</span><span class="python_name">apply_labels</span><span class="python_enclosure">()</span><span class="python_operator">
</span></pre>
    </div>
<p>Let's bake for .0001 seconds and see what rises:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">query</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">fetchall</span><span class="python_enclosure">()</span><span class="python_operator"></span></pre><div class="codepop">SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, addresses.id AS addresses_id, addresses.user_id AS addresses_user_id, addresses.email_address AS addresses_email_address <br/>
FROM users LEFT OUTER JOIN addresses ON users.id = addresses.user_id <br/>
WHERE users.name = ? AND (EXISTS (SELECT addresses.id <br/>
FROM addresses <br/>
WHERE addresses.user_id = users.id AND addresses.email_address LIKE ?)) ORDER BY users.fullname DESC<br/>
['jack', '%@msn.com']</div><pre>

<span class="python_enclosure">[(</span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">u'jack'</span><span class="python_operator">, </span><span class="python_literal">u'Jack Jones'</span><span class="python_operator">, </span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">u'jack@yahoo.com'</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_enclosure">(</span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">u'jack'</span><span class="python_operator">, </span><span class="python_literal">u'Jack Jones'</span><span class="python_operator">, </span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">u'jack@msn.com'</span><span class="python_enclosure">)]</span><span class="python_operator">
</span></pre>
    </div>
<p>So we started small, added one little thing at a time, and at the end we have a huge statement..which actually works.  Now let's do one more thing; the searching function wants to add another <code>email_address</code> criterion on, however it doesn't want to construct an alias of the <code>addresses</code> table; suppose many parts of the application are written to deal specifically with the <code>addresses</code> table, and to change all those functions to support receiving an arbitrary alias of the address would be cumbersome.  We can actually <em>convert</em> the <code>addresses</code> table within the <em>existing</em> statement to be an alias of itself, using <code>replace_selectable()</code>:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">a1 </span><span class="python_operator">= </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">alias</span><span class="python_enclosure">()</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">query </span><span class="python_operator">= </span><span class="python_name">query</span><span class="python_operator">.</span><span class="python_name">replace_selectable</span><span class="python_enclosure">(</span><span class="python_name">addresses</span><span class="python_operator">, </span><span class="python_name">a1</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">query</span><span class="python_operator">
</span></pre><div class="codepop">SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, addresses_1.id AS addresses_1_id, addresses_1.user_id AS addresses_1_user_id, addresses_1.email_address AS addresses_1_email_address <br/>
FROM users LEFT OUTER JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id <br/>
WHERE users.name = :users_name_1 AND (EXISTS (SELECT addresses_1.id <br/>
FROM addresses AS addresses_1 <br/>
WHERE addresses_1.user_id = users.id AND addresses_1.email_address LIKE :addresses_email_address_1)) ORDER BY users.fullname DESC</div><pre>

<span class="python_operator"></span></pre>
    </div>
<p>One more thing though, with automatic labeling applied as well as anonymous aliasing, how do we retrieve the columns from the rows for this thing ?  The label for the <code>email_addresses</code> column is now the generated name <code>addresses_1_email_address</code>; and in another statement might be something different !  This is where accessing by result columns by <code>Column</code> object becomes very useful:
</p>

    

    <div class="code">
        <pre> 
<span class="python_literal"><a href="javascript:togglePopbox('popbox_15', 'show', 'hide')" class="codepoplink">sql</a>&gt;&gt;&gt; </span><span class="python_keyword">for </span><span class="python_name">row </span><span class="python_keyword">in </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">query</span><span class="python_enclosure">)</span><span class="python_operator">:
...     </span><span class="python_keyword">print </span><span class="python_literal">"Name:"</span><span class="python_operator">, </span><span class="python_name">row</span><span class="python_enclosure">[</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">name</span><span class="python_enclosure">]</span><span class="python_operator">, </span><span class="python_literal">"; Email Address"</span><span class="python_operator">, </span><span class="python_name">row</span><span class="python_enclosure">[</span><span class="python_name">a1</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">email_address</span><span class="python_enclosure">]</span><span class="python_operator"></span></pre><div id="popbox_15_div" class="codepop" style="display:none;">SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, addresses_1.id AS addresses_1_id, addresses_1.user_id AS addresses_1_user_id, addresses_1.email_address AS addresses_1_email_address <br/>
FROM users LEFT OUTER JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id <br/>
WHERE users.name = ? AND (EXISTS (SELECT addresses_1.id <br/>
FROM addresses AS addresses_1 <br/>
WHERE addresses_1.user_id = users.id AND addresses_1.email_address LIKE ?)) ORDER BY users.fullname DESC<br/>
['jack', '%@msn.com']</div><pre><span class="python_name">Name</span><span class="python_operator">: </span><span class="python_name">jack </span><span class="python_operator">; </span><span class="python_name">Email Address jack</span><span class="python_operator">@</span><span class="python_name">yahoo</span><span class="python_operator">.</span><span class="python_name">com</span><span class="python_operator">
</span><span class="python_name">Name</span><span class="python_operator">: </span><span class="python_name">jack </span><span class="python_operator">; </span><span class="python_name">Email Address jack</span><span class="python_operator">@</span><span class="python_name">msn</span><span class="python_operator">.</span><span class="python_name">com</span><span class="python_operator">
</span></pre>
    </div>
<p>The above example, by its end, got significantly more intense than the typical end-user constructed SQL will usually be.  However when writing higher-level tools such as ORMs, they become more significant.  SQLAlchemy's ORM relies very heavily on techniques like this.
</p>



            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="sql_everythingelse"></a>
    
    <div class="sectionL2">

    <h3>Everything Else</h3>
    
    

<p>The concepts of creating SQL expressions have been introduced.  What's left are more variants of the same themes.  So now we'll catalog the rest of the important things we'll need to know.
</p>


    
    <A name="sql_everythingelse_bindparams"></a>
    
    <div class="sectionL3">

    <h3>Bind Parameter Objects</h3>
    
    

<p>Throughout all these examples, SQLAlchemy is busy creating bind parameters wherever literal expressions occur.  You can also specify your own bind parameters with your own names, and use the same statement repeatedly.  The database dialect converts to the appropriate named or positional style, as here where it converts to positional for SQLite:
</p>

    

    <div class="code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">from </span><span class="python_name">sqlalchemy</span><span class="python_operator">.</span><span class="python_name">sql </span><span class="python_keyword">import </span><span class="python_name">bindparam</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">s </span><span class="python_operator">= </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">select</span><span class="python_enclosure">(</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">name</span><span class="python_operator">==</span><span class="python_name">bindparam</span><span class="python_enclosure">(</span><span class="python_literal">'username'</span><span class="python_enclosure">))</span><span class="python_operator">
</span><span class="python_literal"><a href="javascript:togglePopbox('popbox_16', 'show', 'hide')" class="codepoplink">sql</a>&gt;&gt;&gt; </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">s</span><span class="python_operator">, </span><span class="python_name">username</span><span class="python_operator">=</span><span class="python_literal">'wendy'</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">fetchall</span><span class="python_enclosure">()</span><span class="python_operator"></span></pre><div id="popbox_16_div" class="codepop" style="display:none;">SELECT users.id, users.name, users.fullname <br/>
FROM users <br/>
WHERE users.name = ?<br/>
['wendy']</div><pre><span class="python_enclosure">[(</span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_literal">u'wendy'</span><span class="python_operator">, </span><span class="python_literal">u'Wendy Williams'</span><span class="python_enclosure">)]</span><span class="python_operator">
</span></pre>
    </div>
<p>Another important aspect of bind parameters is that they may be assigned a type.  The type of the bind parameter will determine its behavior within expressions and also how the data bound to it is processed before being sent off to the database:
</p>

    

    <div class="code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">s </span><span class="python_operator">= </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">select</span><span class="python_enclosure">(</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">name</span><span class="python_operator">.</span><span class="python_name">like</span><span class="python_enclosure">(</span><span class="python_name">bindparam</span><span class="python_enclosure">(</span><span class="python_literal">'username'</span><span class="python_operator">, </span><span class="python_name">type_</span><span class="python_operator">=</span><span class="python_name">String</span><span class="python_enclosure">) </span><span class="python_operator">+ </span><span class="python_name">text</span><span class="python_enclosure">(</span><span class="python_literal">"'%'"</span><span class="python_enclosure">)))</span><span class="python_operator">
</span><span class="python_literal"><a href="javascript:togglePopbox('popbox_17', 'show', 'hide')" class="codepoplink">sql</a>&gt;&gt;&gt; </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">s</span><span class="python_operator">, </span><span class="python_name">username</span><span class="python_operator">=</span><span class="python_literal">'wendy'</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">fetchall</span><span class="python_enclosure">()</span><span class="python_operator"></span></pre><div id="popbox_17_div" class="codepop" style="display:none;">SELECT users.id, users.name, users.fullname <br/>
FROM users <br/>
WHERE users.name LIKE ? || '%'<br/>
['wendy']</div><pre><span class="python_enclosure">[(</span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_literal">u'wendy'</span><span class="python_operator">, </span><span class="python_literal">u'Wendy Williams'</span><span class="python_enclosure">)]</span><span class="python_operator">
</span></pre>
    </div>
<pre><code>
</code></pre><p>Bind parameters of the same name can also be used multiple times, where only a single named value is needed in the execute parameters:
</p>

    

    <div class="code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">s </span><span class="python_operator">= </span><span class="python_name">select</span><span class="python_enclosure">([</span><span class="python_name">users</span><span class="python_operator">, </span><span class="python_name">addresses</span><span class="python_enclosure">]</span><span class="python_operator">, </span>
<span class="python_operator">...    </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">name</span><span class="python_operator">.</span><span class="python_name">like</span><span class="python_enclosure">(</span><span class="python_name">bindparam</span><span class="python_enclosure">(</span><span class="python_literal">'name'</span><span class="python_operator">, </span><span class="python_name">type_</span><span class="python_operator">=</span><span class="python_name">String</span><span class="python_enclosure">) </span><span class="python_operator">+ </span><span class="python_name">text</span><span class="python_enclosure">(</span><span class="python_literal">"'%'"</span><span class="python_enclosure">)) </span><span class="python_operator">| </span>
<span class="python_operator">...    </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">email_address</span><span class="python_operator">.</span><span class="python_name">like</span><span class="python_enclosure">(</span><span class="python_name">bindparam</span><span class="python_enclosure">(</span><span class="python_literal">'name'</span><span class="python_operator">, </span><span class="python_name">type_</span><span class="python_operator">=</span><span class="python_name">String</span><span class="python_enclosure">) </span><span class="python_operator">+ </span><span class="python_name">text</span><span class="python_enclosure">(</span><span class="python_literal">"'@%'"</span><span class="python_enclosure">))</span><span class="python_operator">, </span>
<span class="python_operator">...    </span><span class="python_name">from_obj</span><span class="python_operator">=</span><span class="python_enclosure">[</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">outerjoin</span><span class="python_enclosure">(</span><span class="python_name">addresses</span><span class="python_enclosure">)])</span><span class="python_operator">
</span><span class="python_literal"><a href="javascript:togglePopbox('popbox_18', 'show', 'hide')" class="codepoplink">sql</a>&gt;&gt;&gt; </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">s</span><span class="python_operator">, </span><span class="python_name">name</span><span class="python_operator">=</span><span class="python_literal">'jack'</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">fetchall</span><span class="python_enclosure">()</span><span class="python_operator"></span></pre><div id="popbox_18_div" class="codepop" style="display:none;">SELECT users.id, users.name, users.fullname, addresses.id, addresses.user_id, addresses.email_address <br/>
FROM users LEFT OUTER JOIN addresses ON users.id = addresses.user_id <br/>
WHERE users.name LIKE ? || '%' OR addresses.email_address LIKE ? || '@%'<br/>
['jack', 'jack']</div><pre><span class="python_enclosure">[(</span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">u'jack'</span><span class="python_operator">, </span><span class="python_literal">u'Jack Jones'</span><span class="python_operator">, </span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">u'jack@yahoo.com'</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_enclosure">(</span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">u'jack'</span><span class="python_operator">, </span><span class="python_literal">u'Jack Jones'</span><span class="python_operator">, </span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">u'jack@msn.com'</span><span class="python_enclosure">)]</span><span class="python_operator">
</span></pre>
    </div>



            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="sql_everythingelse_functions"></a>
    
    <div class="sectionL3">

    <h3>Functions</h3>
    
    

<p>SQL functions are created using the <code>func</code> keyword, which generates functions using attribute access:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">from </span><span class="python_name">sqlalchemy</span><span class="python_operator">.</span><span class="python_name">sql </span><span class="python_keyword">import </span><span class="python_name">func</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">func</span><span class="python_operator">.</span><span class="python_name">now</span><span class="python_enclosure">()</span><span class="python_operator">
</span><span class="python_name">now</span><span class="python_enclosure">()</span><span class="python_operator">
</span>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">func</span><span class="python_operator">.</span><span class="python_name">concat</span><span class="python_enclosure">(</span><span class="python_literal">'x'</span><span class="python_operator">, </span><span class="python_literal">'y'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_name">concat</span><span class="python_enclosure">(</span><span class="python_operator">:</span><span class="python_name">param_1</span><span class="python_operator">, :</span><span class="python_name">param_2</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>Certain functions are marked as "ANSI" functions, which mean they don't get the parenthesis added after them, such as CURRENT_TIMESTAMP:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">func</span><span class="python_operator">.</span><span class="python_name">current_timestamp</span><span class="python_enclosure">()</span><span class="python_operator">
</span><span class="python_name">CURRENT_TIMESTAMP</span><span class="python_operator">
</span></pre>
    </div>
<p>Functions are most typically used in the columns clause of a select statement, and can also be labeled as well as given a type.  Labeling a function is recommended so that the result can be targeted in a result row based on a string name, and assigning it a type is required when you need result-set processing to occur, such as for Unicode conversion and date conversions.  Below, we use the result function <code>scalar()</code> to just read the first column of the first row and then close the result; the label, even though present, is not important in this case:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span>
<span class="python_operator">...     </span><span class="python_name">select</span><span class="python_enclosure">([</span><span class="python_name">func</span><span class="python_operator">.</span><span class="python_name">max</span><span class="python_enclosure">(</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">email_address</span><span class="python_operator">, </span><span class="python_name">type_</span><span class="python_operator">=</span><span class="python_name">String</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">label</span><span class="python_enclosure">(</span><span class="python_literal">'maxemail'</span><span class="python_enclosure">)])</span>
<span class="python_operator">... </span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">scalar</span><span class="python_enclosure">()</span><span class="python_operator">
</span></pre><div class="codepop">SELECT max(addresses.email_address) AS maxemail <br/>
FROM addresses<br/>
[]</div><pre>

<span class="python_name">www</span><span class="python_operator">@</span><span class="python_name">www</span><span class="python_operator">.</span><span class="python_name">org</span><span class="python_operator">
</span></pre>
    </div>
<p>Databases such as Postgres and Oracle which support functions that return whole result sets can be assembled into selectable units, which can be used in statements.   Such as, a database function <code>calculate()</code> which takes the parameters <code>x</code> and <code>y</code>, and returns three columns which we'd like to name <code>q</code>, <code>z</code> and <code>r</code>, we can construct using "lexical" column objects as well as bind parameters:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">from </span><span class="python_name">sqlalchemy</span><span class="python_operator">.</span><span class="python_name">sql </span><span class="python_keyword">import </span><span class="python_name">column</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">calculate </span><span class="python_operator">= </span><span class="python_name">select</span><span class="python_enclosure">([</span><span class="python_name">column</span><span class="python_enclosure">(</span><span class="python_literal">'q'</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_name">column</span><span class="python_enclosure">(</span><span class="python_literal">'z'</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_name">column</span><span class="python_enclosure">(</span><span class="python_literal">'r'</span><span class="python_enclosure">)]</span><span class="python_operator">, </span>
<span class="python_operator">...     </span><span class="python_name">from_obj</span><span class="python_operator">=</span><span class="python_enclosure">[</span><span class="python_name">func</span><span class="python_operator">.</span><span class="python_name">calculate</span><span class="python_enclosure">(</span><span class="python_name">bindparam</span><span class="python_enclosure">(</span><span class="python_literal">'x'</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_name">bindparam</span><span class="python_enclosure">(</span><span class="python_literal">'y'</span><span class="python_enclosure">))])</span><span class="python_operator">
</span>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">select</span><span class="python_enclosure">([</span><span class="python_name">users</span><span class="python_enclosure">]</span><span class="python_operator">, </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id </span><span class="python_operator">&gt; </span><span class="python_name">calculate</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">z</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_name">SELECT users</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_operator">, </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">name</span><span class="python_operator">, </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">fullname </span><span class="python_operator">
</span><span class="python_name">FROM users</span><span class="python_operator">, </span><span class="python_enclosure">(</span><span class="python_name">SELECT q</span><span class="python_operator">, </span><span class="python_name">z</span><span class="python_operator">, </span><span class="python_name">r </span>
<span class="python_name">FROM calculate</span><span class="python_enclosure">(</span><span class="python_operator">:</span><span class="python_name">x</span><span class="python_operator">, :</span><span class="python_name">y</span><span class="python_enclosure">)) </span><span class="python_operator">
</span><span class="python_name">WHERE users</span><span class="python_operator">.</span><span class="python_name">id </span><span class="python_operator">&gt; </span><span class="python_name">z</span><span class="python_operator">
</span></pre>
    </div>
<p>If we wanted to use our <code>calculate</code> statement twice with different bind parameters, the <code>unique_params()</code> function will create copies for us, and mark the bind parameters as "unique" so that conflicting names are isolated.  Note we also make two separate aliases of our selectable:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">s </span><span class="python_operator">= </span><span class="python_name">select</span><span class="python_enclosure">([</span><span class="python_name">users</span><span class="python_enclosure">]</span><span class="python_operator">, </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_operator">.</span><span class="python_name">between</span><span class="python_enclosure">(</span>
<span class="python_operator">...    </span><span class="python_name">calculate</span><span class="python_operator">.</span><span class="python_name">alias</span><span class="python_enclosure">(</span><span class="python_literal">'c1'</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">unique_params</span><span class="python_enclosure">(</span><span class="python_name">x</span><span class="python_operator">=</span><span class="python_number">17</span><span class="python_operator">, </span><span class="python_name">y</span><span class="python_operator">=</span><span class="python_number">45</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">z</span><span class="python_operator">, </span>
<span class="python_operator">...    </span><span class="python_name">calculate</span><span class="python_operator">.</span><span class="python_name">alias</span><span class="python_enclosure">(</span><span class="python_literal">'c2'</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">unique_params</span><span class="python_enclosure">(</span><span class="python_name">x</span><span class="python_operator">=</span><span class="python_number">5</span><span class="python_operator">, </span><span class="python_name">y</span><span class="python_operator">=</span><span class="python_number">12</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">z</span><span class="python_enclosure">))</span><span class="python_operator">
</span>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">s</span><span class="python_operator">
</span><span class="python_name">SELECT users</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_operator">, </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">name</span><span class="python_operator">, </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">fullname </span><span class="python_operator">
</span><span class="python_name">FROM users</span><span class="python_operator">, </span><span class="python_enclosure">(</span><span class="python_name">SELECT q</span><span class="python_operator">, </span><span class="python_name">z</span><span class="python_operator">, </span><span class="python_name">r </span>
<span class="python_name">FROM calculate</span><span class="python_enclosure">(</span><span class="python_operator">:</span><span class="python_name">x_1</span><span class="python_operator">, :</span><span class="python_name">y_1</span><span class="python_enclosure">)) </span><span class="python_name">AS c1</span><span class="python_operator">, </span><span class="python_enclosure">(</span><span class="python_name">SELECT q</span><span class="python_operator">, </span><span class="python_name">z</span><span class="python_operator">, </span><span class="python_name">r </span>
<span class="python_name">FROM calculate</span><span class="python_enclosure">(</span><span class="python_operator">:</span><span class="python_name">x_2</span><span class="python_operator">, :</span><span class="python_name">y_2</span><span class="python_enclosure">)) </span><span class="python_name">AS c2 </span><span class="python_operator">
</span><span class="python_name">WHERE users</span><span class="python_operator">.</span><span class="python_name">id BETWEEN c1</span><span class="python_operator">.</span><span class="python_name">z AND c2</span><span class="python_operator">.</span><span class="python_name">z</span><span class="python_operator">
</span>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">s</span><span class="python_operator">.</span><span class="python_name">compile</span><span class="python_enclosure">()</span><span class="python_operator">.</span><span class="python_name">params</span><span class="python_operator">
</span><span class="python_enclosure">{</span><span class="python_literal">'x_2'</span><span class="python_operator">: </span><span class="python_number">5</span><span class="python_operator">, </span><span class="python_literal">'y_2'</span><span class="python_operator">: </span><span class="python_number">12</span><span class="python_operator">, </span><span class="python_literal">'y_1'</span><span class="python_operator">: </span><span class="python_number">45</span><span class="python_operator">, </span><span class="python_literal">'x_1'</span><span class="python_operator">: </span><span class="python_number">17</span><span class="python_enclosure">}</span><span class="python_operator">
</span></pre>
    </div>



            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="sql_everythingelse_unions"></a>
    
    <div class="sectionL3">

    <h3>Unions and Other Set Operations</h3>
    
    

<p>Unions come in two flavors, UNION and UNION ALL, which are available via module level functions:
</p>

    

    <div class="code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">from </span><span class="python_name">sqlalchemy</span><span class="python_operator">.</span><span class="python_name">sql </span><span class="python_keyword">import </span><span class="python_name">union</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">u </span><span class="python_operator">= </span><span class="python_name">union</span><span class="python_enclosure">(</span>
<span class="python_operator">...     </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">select</span><span class="python_enclosure">(</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">email_address</span><span class="python_operator">==</span><span class="python_literal">'foo@bar.com'</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
<span class="python_operator">...    </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">select</span><span class="python_enclosure">(</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">email_address</span><span class="python_operator">.</span><span class="python_name">like</span><span class="python_enclosure">(</span><span class="python_literal">'%@yahoo.com'</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
<span class="python_operator">... </span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">order_by</span><span class="python_enclosure">(</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">email_address</span><span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_literal"><a href="javascript:togglePopbox('popbox_19', 'show', 'hide')" class="codepoplink">sql</a>&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">u</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">fetchall</span><span class="python_enclosure">()</span><span class="python_operator"></span></pre><div id="popbox_19_div" class="codepop" style="display:none;">SELECT addresses.id, addresses.user_id, addresses.email_address <br/>
FROM addresses <br/>
WHERE addresses.email_address = ? UNION SELECT addresses.id, addresses.user_id, addresses.email_address <br/>
FROM addresses <br/>
WHERE addresses.email_address LIKE ? ORDER BY addresses.email_address<br/>
['foo@bar.com', '%@yahoo.com']</div><pre><span class="python_enclosure">[(</span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">u'jack@yahoo.com'</span><span class="python_enclosure">)]</span><span class="python_operator">
</span></pre>
    </div>
<p>Also available, though not supported on all databases, are <code>intersect()</code>, <code>intersect_all()</code>, <code>except_()</code>, and <code>except_all()</code>:
</p>

    

    <div class="code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">from </span><span class="python_name">sqlalchemy</span><span class="python_operator">.</span><span class="python_name">sql </span><span class="python_keyword">import </span><span class="python_name">except_</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">u </span><span class="python_operator">= </span><span class="python_name">except_</span><span class="python_enclosure">(</span>
<span class="python_operator">...    </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">select</span><span class="python_enclosure">(</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">email_address</span><span class="python_operator">.</span><span class="python_name">like</span><span class="python_enclosure">(</span><span class="python_literal">'%@%.com'</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
<span class="python_operator">...    </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">select</span><span class="python_enclosure">(</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">email_address</span><span class="python_operator">.</span><span class="python_name">like</span><span class="python_enclosure">(</span><span class="python_literal">'%@msn.com'</span><span class="python_enclosure">))</span>
<span class="python_operator">... </span><span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_literal"><a href="javascript:togglePopbox('popbox_20', 'show', 'hide')" class="codepoplink">sql</a>&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">u</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">fetchall</span><span class="python_enclosure">()</span><span class="python_operator"></span></pre><div id="popbox_20_div" class="codepop" style="display:none;">SELECT addresses.id, addresses.user_id, addresses.email_address <br/>
FROM addresses <br/>
WHERE addresses.email_address LIKE ? EXCEPT SELECT addresses.id, addresses.user_id, addresses.email_address <br/>
FROM addresses <br/>
WHERE addresses.email_address LIKE ?<br/>
['%@%.com', '%@msn.com']</div><pre><span class="python_enclosure">[(</span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">u'jack@yahoo.com'</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_enclosure">(</span><span class="python_number">4</span><span class="python_operator">, </span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_literal">u'wendy@aol.com'</span><span class="python_enclosure">)]</span><span class="python_operator">
</span></pre>
    </div>



            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="sql_everythingelse_scalar"></a>
    
    <div class="sectionL3">

    <h3>Scalar Selects</h3>
    
    

<p>To embed a SELECT in a column expression, use <code>as_scalar()</code>:
</p>

    

    <div class="code">
        <pre>
<span class="python_literal"><a href="javascript:togglePopbox('popbox_21', 'show', 'hide')" class="codepoplink">sql</a>&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">select</span><span class="python_enclosure">([</span>
<span class="python_operator">...       </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">name</span><span class="python_operator">, </span>
<span class="python_operator">...       </span><span class="python_name">select</span><span class="python_enclosure">([</span><span class="python_name">func</span><span class="python_operator">.</span><span class="python_name">count</span><span class="python_enclosure">(</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_enclosure">)]</span><span class="python_operator">, </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_operator">==</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">as_scalar</span><span class="python_enclosure">()</span>
<span class="python_operator">...    </span><span class="python_enclosure">]))</span><span class="python_operator">.</span><span class="python_name">fetchall</span><span class="python_enclosure">()</span><span class="python_operator"></span></pre><div id="popbox_21_div" class="codepop" style="display:none;">SELECT users.name, (SELECT count(addresses.id) <br/>
FROM addresses <br/>
WHERE users.id = addresses.user_id) AS anon_1 <br/>
FROM users<br/>
[]</div><pre><span class="python_enclosure">[(</span><span class="python_literal">u'jack'</span><span class="python_operator">, </span><span class="python_number">2</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_enclosure">(</span><span class="python_literal">u'wendy'</span><span class="python_operator">, </span><span class="python_number">2</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_enclosure">(</span><span class="python_literal">u'fred'</span><span class="python_operator">, </span><span class="python_number">0</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_enclosure">(</span><span class="python_literal">u'mary'</span><span class="python_operator">, </span><span class="python_number">0</span><span class="python_enclosure">)]</span><span class="python_operator">
</span></pre>
    </div>
<p>Alternatively, applying a <code>label()</code> to a select evaluates it as a scalar as well:
</p>

    

    <div class="code">
        <pre>
<span class="python_literal"><a href="javascript:togglePopbox('popbox_22', 'show', 'hide')" class="codepoplink">sql</a>&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">select</span><span class="python_enclosure">([</span>
<span class="python_operator">...       </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">name</span><span class="python_operator">, </span>
<span class="python_operator">...       </span><span class="python_name">select</span><span class="python_enclosure">([</span><span class="python_name">func</span><span class="python_operator">.</span><span class="python_name">count</span><span class="python_enclosure">(</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_enclosure">)]</span><span class="python_operator">, </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_operator">==</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">label</span><span class="python_enclosure">(</span><span class="python_literal">'address_count'</span><span class="python_enclosure">)</span>
<span class="python_operator">...    </span><span class="python_enclosure">]))</span><span class="python_operator">.</span><span class="python_name">fetchall</span><span class="python_enclosure">()</span><span class="python_operator"></span></pre><div id="popbox_22_div" class="codepop" style="display:none;">SELECT users.name, (SELECT count(addresses.id) <br/>
FROM addresses <br/>
WHERE users.id = addresses.user_id) AS address_count <br/>
FROM users<br/>
[]</div><pre><span class="python_enclosure">[(</span><span class="python_literal">u'jack'</span><span class="python_operator">, </span><span class="python_number">2</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_enclosure">(</span><span class="python_literal">u'wendy'</span><span class="python_operator">, </span><span class="python_number">2</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_enclosure">(</span><span class="python_literal">u'fred'</span><span class="python_operator">, </span><span class="python_number">0</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_enclosure">(</span><span class="python_literal">u'mary'</span><span class="python_operator">, </span><span class="python_number">0</span><span class="python_enclosure">)]</span><span class="python_operator">
</span></pre>
    </div>



            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="sql_everythingelse_correlated"></a>
    
    <div class="sectionL3">

    <h3>Correlated Subqueries</h3>
    
    

<p>Notice in the examples on "scalar selects", the FROM clause of each embedded select did not contain the <code>users</code> table in its FROM clause.  This is because SQLAlchemy automatically attempts to correlate embedded FROM objects to that of an enclosing query.  To disable this, or to specify explicit FROM clauses to be correlated, use <code>correlate()</code>:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">s </span><span class="python_operator">= </span><span class="python_name">select</span><span class="python_enclosure">([</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">name</span><span class="python_enclosure">]</span><span class="python_operator">, </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_operator">==</span><span class="python_name">select</span><span class="python_enclosure">([</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_enclosure">])</span><span class="python_operator">.</span><span class="python_name">correlate</span><span class="python_enclosure">(</span><span class="python_name">None</span><span class="python_enclosure">))</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">s</span><span class="python_operator">
</span><span class="python_name">SELECT users</span><span class="python_operator">.</span><span class="python_name">name </span><span class="python_operator">
</span><span class="python_name">FROM users </span><span class="python_operator">
</span><span class="python_name">WHERE users</span><span class="python_operator">.</span><span class="python_name">id </span><span class="python_operator">= </span><span class="python_enclosure">(</span><span class="python_name">SELECT users</span><span class="python_operator">.</span><span class="python_name">id </span>
<span class="python_name">FROM users</span><span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">s </span><span class="python_operator">= </span><span class="python_name">select</span><span class="python_enclosure">([</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">name</span><span class="python_operator">, </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">email_address</span><span class="python_enclosure">]</span><span class="python_operator">, </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_operator">==</span>
<span class="python_operator">...        </span><span class="python_name">select</span><span class="python_enclosure">([</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_enclosure">]</span><span class="python_operator">, </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_operator">==</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">correlate</span><span class="python_enclosure">(</span><span class="python_name">addresses</span><span class="python_enclosure">)</span>
<span class="python_operator">...    </span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">s</span><span class="python_operator">
</span><span class="python_name">SELECT users</span><span class="python_operator">.</span><span class="python_name">name</span><span class="python_operator">, </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">email_address </span><span class="python_operator">
</span><span class="python_name">FROM users</span><span class="python_operator">, </span><span class="python_name">addresses </span><span class="python_operator">
</span><span class="python_name">WHERE users</span><span class="python_operator">.</span><span class="python_name">id </span><span class="python_operator">= </span><span class="python_enclosure">(</span><span class="python_name">SELECT users</span><span class="python_operator">.</span><span class="python_name">id </span>
<span class="python_name">FROM users </span>
<span class="python_name">WHERE users</span><span class="python_operator">.</span><span class="python_name">id </span><span class="python_operator">= </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>



            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="sql_everythingelse_ordering"></a>
    
    <div class="sectionL3">

    <h3>Ordering, Grouping, Limiting, Offset...ing...</h3>
    
    

<p>The <code>select()</code> function can take keyword arguments <code>order_by</code>, <code>group_by</code> (as well as <code>having</code>), <code>limit</code>, and <code>offset</code>.  There's also <code>distinct=True</code>.  These are all also available as generative functions.  <code>order_by()</code> expressions can use the modifiers <code>asc()</code> or <code>desc()</code> to indicate ascending or descending.  <br></br>
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">s </span><span class="python_operator">= </span><span class="python_name">select</span><span class="python_enclosure">([</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">, </span><span class="python_name">func</span><span class="python_operator">.</span><span class="python_name">count</span><span class="python_enclosure">(</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_enclosure">)])</span><span class="python_operator">.\
...     </span><span class="python_name">group_by</span><span class="python_enclosure">(</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">having</span><span class="python_enclosure">(</span><span class="python_name">func</span><span class="python_operator">.</span><span class="python_name">count</span><span class="python_enclosure">(</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_enclosure">)</span><span class="python_operator">&gt;</span><span class="python_number">1</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">s</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">fetchall</span><span class="python_enclosure">()</span><span class="python_operator"></span></pre><div class="codepop">SELECT addresses.user_id, count(addresses.id) <br/>
FROM addresses GROUP BY addresses.user_id <br/>
HAVING count(addresses.id) &gt; ?<br/>
[1]</div><pre>

<span class="python_enclosure">[(</span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_number">2</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_enclosure">(</span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_number">2</span><span class="python_enclosure">)]</span><span class="python_operator">
</span>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">s </span><span class="python_operator">= </span><span class="python_name">select</span><span class="python_enclosure">([</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">email_address</span><span class="python_operator">, </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_enclosure">])</span><span class="python_operator">.</span><span class="python_name">distinct</span><span class="python_enclosure">()</span><span class="python_operator">.\
...     </span><span class="python_name">order_by</span><span class="python_enclosure">(</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">email_address</span><span class="python_operator">.</span><span class="python_name">desc</span><span class="python_enclosure">()</span><span class="python_operator">, </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">s</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">fetchall</span><span class="python_enclosure">()</span><span class="python_operator"></span></pre><div class="codepop">SELECT DISTINCT addresses.email_address, addresses.id <br/>
FROM addresses ORDER BY addresses.email_address DESC, addresses.id<br/>
[]</div><pre>

<span class="python_enclosure">[(</span><span class="python_literal">u'www@www.org'</span><span class="python_operator">, </span><span class="python_number">3</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_enclosure">(</span><span class="python_literal">u'wendy@aol.com'</span><span class="python_operator">, </span><span class="python_number">4</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_enclosure">(</span><span class="python_literal">u'jack@yahoo.com'</span><span class="python_operator">, </span><span class="python_number">1</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_enclosure">(</span><span class="python_literal">u'jack@msn.com'</span><span class="python_operator">, </span><span class="python_number">2</span><span class="python_enclosure">)]</span><span class="python_operator">
</span>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">s </span><span class="python_operator">= </span><span class="python_name">select</span><span class="python_enclosure">([</span><span class="python_name">addresses</span><span class="python_enclosure">])</span><span class="python_operator">.</span><span class="python_name">offset</span><span class="python_enclosure">(</span><span class="python_number">1</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">limit</span><span class="python_enclosure">(</span><span class="python_number">1</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_keyword">print </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">s</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">fetchall</span><span class="python_enclosure">()</span><span class="python_operator"> </span></pre><div class="codepop">SELECT addresses.id, addresses.user_id, addresses.email_address <br/>
FROM addresses <br/>
LIMIT 1 OFFSET 1<br/>
[]</div><pre>

<span class="python_enclosure">[(</span><span class="python_number">2</span><span class="python_operator">, </span><span class="python_number">1</span><span class="python_operator">, </span><span class="python_literal">u'jack@msn.com'</span><span class="python_enclosure">)]</span><span class="python_operator">
</span></pre>
    </div>



            <a href="#top" class="totoc">back to section top</a>
    </div>




    </div>



    
    <A name="sql_update"></a>
    
    <div class="sectionL2">

    <h3>Updates</h3>
    
    

<p>Finally, we're back to UPDATE.  Updates work a lot like INSERTS, except there is an additional WHERE clause that can be specified.
</p>

    

    <div class="code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_comment"># change 'jack' to 'ed'</span><span class="python_operator">
</span><span class="python_literal"><a href="javascript:togglePopbox('popbox_23', 'show', 'hide')" class="codepoplink">sql</a>&gt;&gt;&gt; </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">update</span><span class="python_enclosure">(</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">name</span><span class="python_operator">==</span><span class="python_literal">'jack'</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_name">name</span><span class="python_operator">=</span><span class="python_literal">'ed'</span><span class="python_enclosure">)</span><span class="python_operator"> </span></pre><div id="popbox_23_div" class="codepop" style="display:none;">UPDATE users SET name=? WHERE users.name = ?<br/>
['ed', 'jack']<br/>
COMMIT</div><pre><span class="python_operator">&lt;</span><span class="python_name">sqlalchemy</span><span class="python_operator">.</span><span class="python_name">engine</span><span class="python_operator">.</span><span class="python_name">base</span><span class="python_operator">.</span><span class="python_name">ResultProxy object at </span><span class="python_number">0x</span><span class="python_operator">...&gt;
</span>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_comment"># use bind parameters</span><span class="python_operator">
</span><span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">u </span><span class="python_operator">= </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">update</span><span class="python_enclosure">(</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">name</span><span class="python_operator">==</span><span class="python_name">bindparam</span><span class="python_enclosure">(</span><span class="python_literal">'oldname'</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_name">values</span><span class="python_operator">=</span><span class="python_enclosure">{</span><span class="python_literal">'name'</span><span class="python_operator">:</span><span class="python_name">bindparam</span><span class="python_enclosure">(</span><span class="python_literal">'newname'</span><span class="python_enclosure">)})</span><span class="python_operator">
</span><span class="python_literal"><a href="javascript:togglePopbox('popbox_24', 'show', 'hide')" class="codepoplink">sql</a>&gt;&gt;&gt; </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">u</span><span class="python_operator">, </span><span class="python_name">oldname</span><span class="python_operator">=</span><span class="python_literal">'jack'</span><span class="python_operator">, </span><span class="python_name">newname</span><span class="python_operator">=</span><span class="python_literal">'ed'</span><span class="python_enclosure">)</span><span class="python_operator"> </span></pre><div id="popbox_24_div" class="codepop" style="display:none;">UPDATE users SET name=? WHERE users.name = ?<br/>
['ed', 'jack']<br/>
COMMIT</div><pre><span class="python_operator">&lt;</span><span class="python_name">sqlalchemy</span><span class="python_operator">.</span><span class="python_name">engine</span><span class="python_operator">.</span><span class="python_name">base</span><span class="python_operator">.</span><span class="python_name">ResultProxy object at </span><span class="python_number">0x</span><span class="python_operator">...&gt;
</span>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_comment"># update a column to an expression</span><span class="python_operator">
</span><span class="python_literal"><a href="javascript:togglePopbox('popbox_25', 'show', 'hide')" class="codepoplink">sql</a>&gt;&gt;&gt; </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">update</span><span class="python_enclosure">(</span><span class="python_name">values</span><span class="python_operator">=</span><span class="python_enclosure">{</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">fullname</span><span class="python_operator">:</span><span class="python_literal">"Fullname: " </span><span class="python_operator">+ </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">name</span><span class="python_enclosure">}))</span><span class="python_operator"> </span></pre><div id="popbox_25_div" class="codepop" style="display:none;">UPDATE users SET fullname=(? || users.name)<br/>
['Fullname: ']<br/>
COMMIT</div><pre><span class="python_operator">&lt;</span><span class="python_name">sqlalchemy</span><span class="python_operator">.</span><span class="python_name">engine</span><span class="python_operator">.</span><span class="python_name">base</span><span class="python_operator">.</span><span class="python_name">ResultProxy object at </span><span class="python_number">0x</span><span class="python_operator">...&gt;
</span></pre>
    </div>


    
    <A name="sql_update_correlated"></a>
    
    <div class="sectionL3">

    <h3>Correlated Updates</h3>
    
    

<p>A correlated update lets you update a table using selection from another table, or the same table:
</p>

    

    <div class="code">
        <pre>
<span class="python_literal">&gt;&gt;&gt; </span><span class="python_name">s </span><span class="python_operator">= </span><span class="python_name">select</span><span class="python_enclosure">([</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">email_address</span><span class="python_enclosure">]</span><span class="python_operator">, </span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">==</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">limit</span><span class="python_enclosure">(</span><span class="python_number">1</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_literal"><a href="javascript:togglePopbox('popbox_26', 'show', 'hide')" class="codepoplink">sql</a>&gt;&gt;&gt; </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">update</span><span class="python_enclosure">(</span><span class="python_name">values</span><span class="python_operator">=</span><span class="python_enclosure">{</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">fullname</span><span class="python_operator">:</span><span class="python_name">s</span><span class="python_enclosure">}))</span><span class="python_operator"> </span></pre><div id="popbox_26_div" class="codepop" style="display:none;">UPDATE users SET fullname=(SELECT addresses.email_address <br/>
FROM addresses <br/>
WHERE addresses.user_id = users.id <br/>
LIMIT 1 OFFSET 0)<br/>
[]<br/>
COMMIT</div><pre><span class="python_operator">&lt;</span><span class="python_name">sqlalchemy</span><span class="python_operator">.</span><span class="python_name">engine</span><span class="python_operator">.</span><span class="python_name">base</span><span class="python_operator">.</span><span class="python_name">ResultProxy object at </span><span class="python_number">0x</span><span class="python_operator">...&gt;
</span></pre>
    </div>



            <a href="#top" class="totoc">back to section top</a>
    </div>




    </div>



    
    <A name="sql_delete"></a>
    
    <div class="sectionL2">

    <h3>Deletes</h3>
    
    

<p>Finally, a delete.  Easy enough:
</p>

    

    <div class="code">
        <pre>
<span class="python_literal"><a href="javascript:togglePopbox('popbox_27', 'show', 'hide')" class="codepoplink">sql</a>&gt;&gt;&gt; </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">addresses</span><span class="python_operator">.</span><span class="python_name">delete</span><span class="python_enclosure">())</span><span class="python_operator"> </span></pre><div id="popbox_27_div" class="codepop" style="display:none;">DELETE FROM addresses<br/>
[]<br/>
COMMIT</div><pre><span class="python_operator">&lt;</span><span class="python_name">sqlalchemy</span><span class="python_operator">.</span><span class="python_name">engine</span><span class="python_operator">.</span><span class="python_name">base</span><span class="python_operator">.</span><span class="python_name">ResultProxy object at </span><span class="python_number">0x</span><span class="python_operator">...&gt;
</span>
<span class="python_literal"><a href="javascript:togglePopbox('popbox_28', 'show', 'hide')" class="codepoplink">sql</a>&gt;&gt;&gt; </span><span class="python_name">conn</span><span class="python_operator">.</span><span class="python_name">execute</span><span class="python_enclosure">(</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">delete</span><span class="python_enclosure">(</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">name </span><span class="python_operator">&gt; </span><span class="python_literal">'m'</span><span class="python_enclosure">))</span><span class="python_operator"> </span></pre><div id="popbox_28_div" class="codepop" style="display:none;">DELETE FROM users WHERE users.name &gt; ?<br/>
['m']<br/>
COMMIT</div><pre><span class="python_operator">&lt;</span><span class="python_name">sqlalchemy</span><span class="python_operator">.</span><span class="python_name">engine</span><span class="python_operator">.</span><span class="python_name">base</span><span class="python_operator">.</span><span class="python_name">ResultProxy object at </span><span class="python_number">0x</span><span class="python_operator">...&gt;
</span></pre>
    </div>



            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="sql_reference"></a>
    
    <div class="sectionL2">

    <h3>Further Reference</h3>
    
    

<p>The best place to get every possible name you can use in constructed SQL is the <a href="sqlalchemy_sql_expression.html#docstrings_sqlalchemy.sql.expression">Generated Documentation</a>.
</p>
<p>Table Metadata Reference: <a href="metadata.html">Database Meta Data</a>
</p>
<p>Engine/Connection/Execution Reference: <a href="dbengine.html">Database Engines</a>
</p>
<p>SQL Types: <a href="types.html">The Types System</a>
</p>




            <a href="#top" class="totoc">back to section top</a>
    </div>




    </div>





    <div class="bottomnav">
        
    <div class="prevnext">

            
            Previous: <a href="ormtutorial.html">Object Relational Tutorial</a>

               |   
            Next: <a href="mappers.html">Mapper Configuration</a>
    </div>

    </div>






</body>
</html>






